Listing 1: DynamicEmployeesList.sql

CREATE PROCEDURE DynamicEmployeesList 
   @EmployeeID int=NULL  ,
   @LastName nvarchar (20)=NULL  ,
   @FirstName nvarchar (10)=NULL,
   @Title nvarchar (30)=NULL  ,
   @Address nvarchar (60)=NULL ,
   @City nvarchar (15)=NULL  ,
   @Region nvarchar (15) =NULL,
   @PostalCode nvarchar (10)=NULL ,
   @Country nvarchar (15) =NULL 
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(3000)

SET @SQL = N'SELECT EmployeeID,LastName,FirstName, Title, BirthDate,'
SET @SQL = @SQL + 'HireDate,Address, City, Region, PostalCode, Country FROM  
EMPLOYEES'
SET @WhereClause = N' WHERE 1=1' 

IF @EmployeeID IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND EmployeeID = ' + 
      cast(@EmployeeID as nvarchar(100))

IF @LastName IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND LastName like ''%' + @LastName + '%'''

IF @FirstName IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND FirstName like ''%' + @FirstName + '%'''

IF @Title IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND Title like  ''%' + @Title + '%''' 

IF @Address IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND Address like '' %' + @Address + '% '''

IF @City IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND city like ''%' + @City + '%'''

IF @Region IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND Region like ''%' + @Region + '%'''

IF @PostalCode IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND PostalCode = ''' + @PostalCode + ''''

IF @Country IS NOT NULL
   SET @WhereClause = @WhereClause + ' AND Country = ''' + @Country + ''''
EXEC(@SQL + @WhereClause)
Go