CODEWITHSUNDEEP
sqlsql-servert-sql
Hamed Rashno
Hamed Rashno

Reputation: 344

How to optional "AND" or "OR" between conditions of where in stored procedure with parameter?

I have a stored procedure to return results filtered by some parameter (all parameters are optional).

Sometimes I want to get results where condition1 AND condition2 are true, and sometimes when only one of conditions is true.

SELECT 
    *
FROM
    ProductAndServices
WHERE
    Title       = ISNULL(@Title,Title)         @AndOR 
    CreatedBy   = ISNULL(@CreatedBy,CreatedBy)

Upvotes: 1

Views: 492

Answers (5)

Bart Hofland
Bart Hofland

Reputation: 3905

You could introduce a variable/parameter that specifies if you want to use AND or OR logic with your fields and incorporate that parameter in your WHERE-clause as well.

Something like this, for example:

-- Declared a variable here, but might be a stored procedure parameter as well
DECLARE @And BIT = 1 -- When 0, WHERE uses OR; when 1, WHERE uses AND

SELECT 
    *
FROM
    ProductAndServices
WHERE
    (@And = 0 AND (Title = ISNULL(@Title, Title) OR  CreatedBy = ISNULL(@CreatedBy, CreatedBy))) OR
    (@And = 1 AND (Title = ISNULL(@Title, Title) AND CreatedBy = ISNULL(@CreatedBy, CreatedBy)))
OPTION (RECOMPILE)

Furthermore, I would avoid using functions like ISNULL in the WHERE-clause, because it might prevent the query optimizer to use indexes.

So instead of this:

Title = ISNULL(@Title, Title)
CreatedBy = ISNULL(@CreatedBy, CreatedBy)

I would use:

(@Title IS NULL OR Title = @Title)
(@CreatedBy IS NULL OR CreatedBy = @CreatedBy)

With this, the query would become:

SELECT 
    *
FROM
    ProductAndServices
WHERE
    (@And = 0 AND ((@Title IS NULL OR Title = @Title) OR  (@CreatedBy IS NULL OR CreatedBy = @CreatedBy))) OR
    (@And = 1 AND ((@Title IS NULL OR Title = @Title) AND (@CreatedBy IS NULL OR CreatedBy = @CreatedBy)))
OPTION (RECOMPILE)

So this latter query is longer and not as readable as the former query, but it should perform better. I think. I have not tested it. You might want to benchmark it with several datasets (and check out the corresponding execution plans) to be sure.

Edit:

Based on a tip by Marc Guillot, I added OPTION (RECOMPILE) to the query. You could check out the Query Hints documentation in Microsoft Docs for more info about it.

I also found the article Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues by Robin Lester just now. Haven't read it completely yet, but after scanning it quickly, it looks like a good read to me.

Upvotes: 2

Guruprakash Chinnasamy
Guruprakash Chinnasamy

Reputation: 144

Write as SQL Statement like below 

DECLARE @str VARCHAR(max)=N'SELECT *
                               FROM ProductAndServices
                               WHERE  Title='+ CASE WHEN  @Tittle IS NULL THEN 'Title' ELSE '''' + @Tittle  + '''' END
        + ' AND CreatedBy='+ CASE WHEN  @CreatedBy IS NULL THEN 'CreatedBy' ELSE '''' + @CreatedBy  + '''' END

PRINT @str;

Upvotes: 0

MJoy
MJoy

Reputation: 1369

The following query should do what you want,

SELECT *
FROM ProductAndServices
WHERE
    (@Title IS NULL OR Title = @Title) AND
    (@CreatedBy IS NULL OR CreatedBy = @CreatedBy)

Upvotes: 0

gotqn
gotqn

Reputation: 43636

You can try building dynamic T-SQL statement in your routine. It is a little more difficult to write and debug, but it will lead to more simple T-SQL statements being execute and from there - possibility for better performance. Here is an example:

DECLARE @Tittle VARCHAR(12)
       ,@CreatedBy VARCHAR(12)
       ,@AndOR VARCHAR(12)

SELECT @Tittle = 'Here comes the sun'
      ,@CreatedBy = 'Beatles'
      ,@AndOR = 'AND';

SELECT @Tittle = ISNULL(@Tittle, '')
      ,@CreatedBy = ISNULL(@CreatedBy, '')
      ,@AndOR = ISNULL(@AndOR, 'AND');

DECLARE @DynammicTSQLStatement NVARCHAR(MAX);

SET @DynammicTSQLStatement =N'
SELECT 
    *
FROM
    ProductAndServices
WHERE ' + CASE WHEN  @Tittle = '' THEN '' ELSE 'Title = ''' + @Tittle  + '''' END
        + CASE WHEN @Tittle <> '' AND @CreatedBy <> '' THEN ' ' + @AndOR + ' CreatedBy = ''' + @CreatedBy + '''' ELSE '' END;

SELECT  @DynammicTSQLStatement

EXEC sp_executesql @DynammicTSQLStatement;

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

Use if condition for your case:

    GO

    if (@Condtion)
    Begin
    SELECT * FROM ProductAndServices
    WHERE
        Title       = ISNULL(@Title,Title) And
        CreatedBy   = ISNULL(@CreatedBy,CreatedBy)  

    End
    Else
    Begin
    SELECT * FROM ProductAndServices
    WHERE
        Title       = ISNULL(@Title,Title) OR
        CreatedBy   = ISNULL(@CreatedBy,CreatedBy)  

    End

I am sure you know your sometimes when you want to use AND and when to use OR.

Upvotes: 0

Related Questions