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