Sqlserver过程动态where条件

CREATE PROCEDURE GetEmployees
    @FirstName VARCHAR(50) = NULL,
    @LastName VARCHAR(50) = NULL,
    @Department VARCHAR(50) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

    IF @FirstName IS NOT NULL
        SET @SQL = @SQL + ' AND FirstName = ''' + @FirstName + ''''

    IF @LastName IS NOT NULL
        SET @SQL = @SQL + ' AND LastName = ''' + @LastName + ''''

    IF @Department IS NOT NULL
        SET @SQL = @SQL + ' AND Department = ''' + @Department + ''''

    EXEC sp_executesql @SQL
END