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
This question is related to
sql
sql-server
if-statement
where-clause
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,'') = ''
Old post but worth a look for someone who stumbles upon like me
ISNULL(NULLIF(ColumnName, ' '), NULL) IS NOT NULL
ISNULL(NULLIF(ColumnName, ' '), NULL) IS NULL
If you can use some dynamic query, you can use LEN
. It will give false on both empty and null string. By this way you can implement the option parameter.
ALTER PROCEDURE [dbo].[psProducts]
(@SearchType varchar(50))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Query nvarchar(max) = N'
SELECT
P.[ProductId],
P.[ProductName],
P.[ProductPrice],
P.[Type]
FROM [Product] P'
-- if @Searchtype is not null then use the where clause
SET @Query = CASE WHEN LEN(@SearchType) > 0 THEN @Query + ' WHERE p.[Type] = ' + ''''+ @SearchType + '''' ELSE @Query END
EXECUTE sp_executesql @Query
PRINT @Query
END
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
)
AS
BEGIN
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]);
END
GO
Now if you pass NULL
, an empty string (''
), or leave out the parameter, the where clause will essentially be ignored.
WHERE p.[Type] = isnull(@SearchType, p.[Type])
Source: Stackoverflow.com