How to write Search Procedure in SQL Server

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchProcedure]

(

@EmpName VARCHAR(50)=NULL,

@DOB DATETIME=NULL,

@Salary MONEY=NULL

)

AS

BEGIN

DECLARE @Query VARCHAR(2000)

DECLARE @Cond VARCHAR(1000)

SET @Query ='SELECT EmpID,

ISNULL(EmpName,'''') AS EmpName,

DOB,

Salary,

DeptID

FROM tblEmployee'

SET @Cond=' WHERE 1=1 '

-- For string type values

IF(ISNULL(@EmpName,'')<>'')

BEGIN

SET @Cond=@Cond+' AND EmpName LIKE ' +'''%' + @EmpName + '%'''

END

-- For Nuemric type values

IF(ISNULL(@Salary,0)<>0)

BEGIN

SET @Cond = @Cond + ' AND Salary = ' + CONVERT(VARCHAR(10),@Salary)

END

-- For Date and Time values

IF(ISNULL(@DOB,'')<>'')

BEGIN

SET @Cond = @Cond + ' AND DATEDIFF(DD,DOB, CONVERT(DATETIME,''' + CONVERT(VARCHAR,@DOB) +''')) = 0 '

END

SET @Query=@Query+@Cond

EXEC(@Query)

END