User970008 User970008 - 2 months ago 15
SQL Question

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

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

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,'') = ''