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.

Concatenating row values from table to single string using T-SQL

SQL query concatenate row value to string

We can do this in many ways , you can write SQL function , COALESCE , XML PATH.
So this time  I'm give example using XML PATH and COALESCE. I use NORTHWND sample database to provide example

Example 1 : COALESCE (find more about COALESCE)

Using COALESCE function , we can concatenate many row values in to one single string. this is one use of COALESCE , there are many ,so you can find some more clicking above mention link.

Example 2: XML PATH

 1


2

In second example you can see I used STUFF function . So I used it for remove extra comma from string. Usually STUFF function does  inserts a string into another string, it deletes first string character form begin for given length and put second string into it

Common Table Expression (CTE) in Sql Server

What is Common Table Expression (CTE)

• We can take CTE as tempory view (can be thought as tempory result set) that create in memory

• Its scope valid until next query run

• CTE need to use just after it declaration  

• There are two type Recursive and Nonrecursive CTE

• Can define multiple CTE

• CTE are unindexable ( it cau use exist index in referenced object)

• CTE can't have constrain

• ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword

Simple Example

ex 1

WITH CTE_CUSTOMER_EX2
(         [CustomerID]
          ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]
)
AS
(
SELECT [CustomerID]
  ,[CompanyName]
  ,[ContactName]
  ,[ContactTitle]
  ,[Address]
  ,[City]

  FROM .[dbo].[Customers]
)


SELECT * FROM CTE_CUSTOMER_EX2

ex 2

;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
 ,[CompanyName]
 ,[ContactName]
 FROM .[dbo].[Customers]

)
SELECT * FROM CTE_CUSTOMER_EX1

Note
If there are query before CTE define , need to use Semicolon teminator before declare CTE


Multiple CTE

  With CTE_M1 as 
(
-select statement
) 
     , CTE_M2 as 
(
-select statemet

)

you can find some more detail and examples using this Link and Link2