Search code examples
sql-serverrdbms

How to handle null values in Sql Server like operator

I have created a stored procedure shown below it provides all the menus based on search of 'Text' and 'MenuType' columns.

I want to create it like if I pass GetMenusbyTextandType '','-1' as paramters then it should show all the records of MenuMaster table. The problem is that I have some null values in 'MenuType' column, which are not reflecting in this proc.

Create proc GetMenusbyTextandType
    @Text nvarchar(400),
    @MenuType nvarchar(10)
as
begin
    SELECT * 
    FROM MenuMaster 
    WHERE Active = 1 
      AND [Text] LIKE '%' + @Text + '%' 
      AND MenuType LIKE 
             CASE 
                WHEN @MenuType = '-1' 
                   THEN '%' + ISNULL(MenuType, '') + '%' 
                   ELSE '%' + @MenuType + '%' 
             END
END

enter image description here

Solution

  • You are over-complicating your sql. There is a simple solution:

    SELECT * 
FROM MenuMaster 
WHERE Active = 1 
  AND [Text] LIKE '%' + @Text + '%' 
  AND (
      @MenuType = '-1' 
      OR MenuType LIKE '%' + @MenuType + '%' 
  )