Alex Rivera | Logout

SQL Server : check if variable is Empty or NULL for WHERE clause

Asked 2012-04-23T16:09:17.867
41

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
sql sql-server if-statement where-clause
Edit
Report

1 Answer

17

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])
  OPTION (RECOMPILE);
END
GO

Now if you pass NULL, an empty string (''), or leave out the parameter, the where clause will essentially be ignored.

I added OPTION (RECOMPILE) to demonstrate that seeks are possible with the right parameter values and provided the index covers the query adequately, however a seek is very unlikely if the compilation occurred for NULL (whole table) or a parameter value that returned too many rows to make a seek worthwhile. Give it a try.

enter image description here

In reality, though, optional parameters that tend to lead to scans when they shouldn't should almost certainly be handled with with a combination of dynamic SQL and, for parameters where data skew can be an issue, OPTION (RECOMPILE). See my "kitchen sink" approach here, and give it a try:

answered 2012-04-23T16:19:33.880

Your Answer