Better way to write a Query with 4 possible paths?

Question

May not have been the best title but anyways..

I have a stored procedure which until now had 1 optional parameter which I easily solved with 1 if statement. Now it has 2 which brings it up to 4 if statements. I would like to know if there is a better way to write a query then the following. Especially if/when a variable count goes up to 4 or 5.

I am looking for an easier way to add this new ignoreTheseIds variable without having to add 2 more if/else statements.

ALTER PROCEDURE [dbo].[SomeQuery]
    @StartingDate varchar(10) = '1/1/1900',
    @EndingDate varchar(10) = NOW,
    @LimitToTheseIds varchar(MAX) = ''
    @IgnoreTheseIds varchar(MAX) = ''
AS

BEGIN
    SET NOCOUNT ON;

    IF @LimitTo = ''
    BEGIN
        SELECT id1, col2
        FROM Table1 as T
        WHERE SomeDateTime <= @EndDate
          AND SomeDateTime >= @StartDate
    END
    ELSE
    BEGIN
        SELECT id1, col2
        FROM Table1 as T
        WHERE SomeDateTime <= @EndDate
            AND SomeDateTime >= @StartDate
            AND @LimitToTheseIds LIKE '%|' + CAST(id1 as varchar) + '|%'
    END
END

Hope I didn't mess anything up when changing the var names...

Answer 1

When I've had to write larger SPROCS like this, I've added a "Type" variable that would go to a particular statement within the SPROC. For instance:

@queryType = 'A'

if @queryType = 'A'
 begin
  --do query A
 end

if @queryType = 'B'
 begin
  --do query B
 end

--etc.

It ended up being a little cleaner than having multiple if trees.

Answer 2

You can use the CASE statement for this, although you'll need to check to see whether you take efficiency hits by so doing. Something like this:

ALTER PROCEDURE [dbo].[SomeQuery]
    @StartingDate varchar(10) = '1/1/1900',
    @EndingDate varchar(10) = NOW,
    @LimitToTheseIds varchar(MAX) = ''
    @IgnoreTheseIds varchar(MAX) = ''
AS

BEGIN
    SET NOCOUNT ON;

    SELECT id1, col2
    FROM Table1 as T
    WHERE SomeDateTime <= @EndDate
        AND SomeDateTime >= @StartDate
        AND @LimitToTheseIds LIKE CASE WHEN @LimitToTheseIds = '' THEN '' ELSE '%|' + CAST(id1 as varchar) + '|%' END
        AND @IgnoreTheseIds LIKE CASE WHEN @IgnoreTheseIds = '' THEN '' ELSE /*something appropriate here*/
END

etc.

Answer 3

You can check to see if anything is passed in at all:

ALTER PROCEDURE [dbo].[SomeQuery] 
    @StartingDate varchar(10) = '1/1/1900', 
    @EndingDate varchar(10) = NOW, 
    @LimitToTheseIds varchar(MAX) = '' 
    @IgnoreTheseIds varchar(MAX) = '' 
AS 

BEGIN 
    SET NOCOUNT ON; 

    SELECT id1, col2 
    FROM Table1 as T 
    WHERE SomeDateTime <= @EndDate 
        AND SomeDateTime >= @StartDate 
        AND (@LimitToTheseIds LIKE '%|' + CAST(id1 as varchar) + '|%' 
        OR @LimitToTheseId = '')
END 

Answer 4

  SELECT id1, col2
FROM Table1 as T
WHERE SomeDateTime <= @EndDate
    AND SomeDateTime >= @StartDate
AND (@LimitToTheseIds IS NULL OR @LimitToTheseIds LIKE '%|' + CAST(id1 as varchar) + '|%')

Answer 5

this is the most comprehensive article on dynamic search condition that I've ever come across:

http://www.sommarskog.se/dyn-search.html