Dynamic WHERE clause with T-SQL

Dynamic where clause in T-SQL

There many ways to do Dynamic WHERE clause with T-SQL , So this time I'm going to use

• COALESCE
• OR Operator
• ISNULL
• CASE
• Dynamic SQL query

Note - All example I have done with NORTHWIND database

1 . COALESCE

DECLARE @OrderID int,
                   @ProductID int


SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
FROM [dbo].[OrderDetails]
WHERE OrderID = COALESCE(@OrderID , OrderID)
               AND [ProductID] =COALESCE(@ProductID , [ProductID])

   Find out more about COALESCE

2. OR Operator


DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
                 ,[ProductID]
                 ,[UnitPrice]
                 ,[Quantity]
                 ,[Discount]
FROM    [dbo].[OrderDetails]
WHERE  ((@OrderID IS NULL) OR (OrderID = @OrderID))
                 AND ((@ProductID IS NULL) OR (ProductID =@ProductID))


3. ISNULL


DECLARE @OrderID int,
                   @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
               ,[ProductID]
               ,[UnitPrice]
               ,[Quantity]
               ,[Discount]
 FROM   [dbo].[OrderDetails]
 WHERE OrderID = ISNULL(@OrderID , OrderID)
                AND [ProductID] =ISNULL(@ProductID , [ProductID])

Find out more about ISNULL

4. CASE

 DECLARE @OrderID int,
                    @ProductID int

SET @OrderID =null
SET @ProductID =null

SELECT [OrderID]
             ,[ProductID]
             ,[UnitPrice]
             ,[Quantity]
             ,[Discount]
 FROM [dbo].[OrderDetails]
 WHERE OrderID = CASE WHEN @OrderID IS NOT NULL THEN @OrderID ELSE OrderID                                          END
                                  AND [ProductID] =CASE WHEN @ProductID IS NOT NULL THEN                                                 @ProductID ELSE ProductID END


5. Dynamic SQL Query

DECLARE @OrderID int,
                   @ProductID int ,
                   @DyWhere nvarchar(MAX),
                   @DyQuery nvarchar(MAX)

SET @OrderID =10249
SET @ProductID =NULL
SET @DyWhere =' WHERE'

SET @DyQuery ='SELECT [OrderID]
                                             ,[ProductID]
                                             ,[UnitPrice]
                                             ,[Quantity]
                                             ,[Discount]
                                              FROM [dbo].[OrderDetails]'


IF @OrderID IS NOT NULL
BEGIN
   SET @DyWhere =@DyWhere+ ' OrderID ='+CONVERT(nvarchar, @OrderID)
 
END

IF @ProductID IS NOT NULL
BEGIN
SET @DyWhere =@DyWhere + ' AND ProductID ='+CONVERT(nvarchar, @ProductID)
END

IF @OrderID IS NOT NULL OR @ProductID IS NOT NULL
BEGIN
SET @DyQuery =@DyQuery +@DyWhere
END


EXECUTE sp_ExecuteSQL @DyQuery

Find out more about  sp_ExecuteSQL



You can use above mentioned one of method according to your scenario , Last one (dynamic SQL query ) this can use for more dynamic scenarios.