SQL Server : check if variable is Empty or NULL for WHERE clause
Just use
If @searchType is null means 'return the whole table' then use
WHERE p.[Type] = @SearchType OR @SearchType is NULLIf @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,'') = ''
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)ASBEGIN 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]);ENDGONow if you pass NULL, an empty string (''), or leave out the parameter, the where clause will essentially be ignored.