0

I want to create a stored procedure to query 'running' tasks. Additionally I want the procedure to have two optional filter parameters to only get running tasks for certain users and task types (airflow ids). Here is what I have so far:

CREATE PROCEDURE [dbo].[spTask_GetRunning]
    @UserID NVARCHAR(128) NULL,
    @AirflowDagID INT NULL
    AS
BEGIN
    SELECT ID, UserID, AirflowDagID, [Name], [StartTime], [FinishedTime], [Status], [Parameter]
    FROM dbo.[Task]
    WHERE [Status] = 'running'
        AND (
            @UserID IS NULL OR
            UserID = @UserID
        )
        AND (
            @AirflowDagID IS NULL OR
            AirflowDagID = @AirflowDagID
    )
END

This seems to work fine, however since this is the first time I'm in need of optional parameters and posts on the topic seem quite old, I'm wondering if this is the recommended way to go. For example, this post suggest creating different queries for better optimization (though again the post is quite old).

Stu
  • 30,392
  • 6
  • 14
  • 33
Roland Deschain
  • 2,211
  • 19
  • 50
  • 1
    It seems that this is about sql server. Please fix your tags. – iDevlop Jul 14 '22 at 09:26
  • @Stu In principle it does, as mentioned in the post there are many answers to this question. I just wanted to make sure these posts are still relevant today. – Roland Deschain Jul 14 '22 at 09:33
  • 1
    Yes they are, see also https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example, the dynamic SQL version is probably the most efficient, if the most complex and difficult to manage. Entirely separate stored procedures might be an option – Charlieface Jul 14 '22 at 09:45

0 Answers0