0

I am writing a stored procedure in SQL Server 2014 and is given below

CREATE PROCEDURE [dbo].[GetUsers]
@Role INT
    SELECT FirstName, LastName  
    FROM Users 
    WHERE FirstName = 'Something'
    IF NULLIF(@Role, '') IS NOT NULL    
    BEGIN
        AND Role = @Role
    END
 END

With this it is throwing Syntax error at AND

I want to build the query dynamically and exclude NULL and '' values

CPK_2011
  • 872
  • 3
  • 21
  • 57

1 Answers1

1

You can't build a dynamic query like that. But you can use boolean logic

SELECT FirstName, LastName  
FROM Users 
Where FirstName = 'Something'
AND (@Role is null or @Role = '' or Role = @Role)
juergen d
  • 201,996
  • 37
  • 293
  • 362