SQL Server check if variable is Empty or NULL for WHERE clause

Question

When searching for a list of products  the  SearchType parameter is optional  If  SearchType is empty or NULL then it should return all products and not use the WHERE clause  Otherwise  if it passed Equipment it would then use that instead   ALTER PROCEDURE  dbo   psProducts         SearchType varchar 50   AS BEGIN     SET NOCOUNT ON       SELECT          P  ProductId           P  ProductName           P  ProductPrice           P  Type      FROM  Product  P     -- if  Searchtype is not null then use the where clause     WHERE p  Type     SearchType END

User · Accepted Answer

Just use  If  searchType is null means  return the whole table  then use  WHERE p  Type     SearchType OR  SearchType is NULL   If  searchType is an empty string means  return the whole table  then use  WHERE p  Type     SearchType OR  SearchType        If  searchType is null or an empty string means  return the whole table  then use  WHERE p  Type     SearchType OR Coalesce  SearchType

User · Answer

If you can use some dynamic query  you can use LEN   It will give false on both empty and null string  By this way you can implement the option parameter   ALTER PROCEDURE  dbo   psProducts     SearchType varchar 50   AS BEGIN     SET NOCOUNT ON   DECLARE  Query nvarchar max    N      SELECT          P  ProductId           P  ProductName           P  ProductPrice           P  Type      FROM  Product  P      -- if  Searchtype is not null then use the where clause     SET  Query   CASE WHEN LEN  SearchType   gt  0 THEN  Query     WHERE p  Type               SearchType        ELSE  Query END         EXECUTE sp executesql  Query     PRINT  Query END

User · Answer

WHERE p  Type    isnull  SearchType  p  Type

User · Answer

Old post but worth a look for someone who stumbles upon like me   ISNULL NULLIF ColumnName        NULL  IS NOT NULL  ISNULL NULLIF ColumnName        NULL  IS NULL

User · Answer

If you don t want to pass the parameter when you don t want to search  then you should make the parameter optional instead of assuming that    and NULL are the same thing   ALTER PROCEDURE  dbo   psProducts        SearchType varchar 50    NULL   AS BEGIN   SET NOCOUNT ON     SELECT P  ProductId     P  ProductName     P  ProductPrice     P  Type    FROM dbo  Product  AS P   WHERE p  Type    COALESCE NULLIF  SearchType       p  Type    END GO   Now if you pass NULL  an empty string       or leave out the parameter  the where clause will essentially be ignored

[sql] SQL Server : check if variable is Empty or NULL for WHERE clause

Examples related to sql

Examples related to sql-server

Examples related to if-statement

Examples related to where-clause