Listing 2: StaticEmployeesList.sql

CREATE PROCEDURE StaticEmployeesList 
   @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 @pLastName nvarchar (30)  ,
   @pFirstName nvarchar (20) ,
   @pTitle nvarchar (40)   ,
   @pAddress nvarchar (70)  ,
   @pCity nvarchar (25)   ,
   @pRegion nvarchar (25) 

IF @LastName IS NOT NULL
   set @pLastName = '%'+@LastName+'%'

IF @FirstName IS NOT NULL
   set @pFirstName = '%'+@FirstName+'%'

IF @Title IS NOT NULL
   set @pTitle = '%'+@Title+'%'

IF @Address IS NOT NULL
   set @pAddress = '%'+@Address+'%'

IF @City IS NOT NULL
   set @pCity = '%'+@City+'%'

IF @Region IS NOT NULL
   set @pRegion = '%'+@Region+'%'

SELECT EmployeeID,LastName,FirstName, Title, BirthDate, 
   HireDate,Address, City, Region, PostalCode, Country FROM  EMPLOYEES 
WHERE 
   (@EmployeeID is null or EmployeeID =@EmployeeID)
   AND (@LastName is null or LastName like  @pLastName)
   AND (@FirstName is null or FirstName like  @pFirstName)
   AND (@Title is null or Title like  @pTitle)
   AND (@Address is null or Address like  @pAddress)
   AND (@City is null or City like  @pCity)
   AND (@Region is null or Region like  @pRegion)   
   AND (@PostalCode is null or PostalCode =  @PostalCode)   
   AND (@Country is null or Country =  @Country)
Go

execute DynamicEmployeesList NULL,'R'

execute StaticEmployeesList  NULL,'R'