If variable is Empty or NULL in WHERE clause in SQL Server
If @SearchType is Null or Empty how to return all values from the database.
Sample Query:
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
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,'') = ''
If the @SearchType is Null or Empty, it returns all the value from the database.
If the @SearchType contains any value, it returns only the condition satisfied data.
