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

Answer 1

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