CREATE PROCEDURE sps_empsearch
(
@empcode varchar(20),
@firstname varchar(50),
@lastname varchar(50)
)
AS
BEGIN TRY
if(@empcode='')
set @empcode=null
if(@firstname='')
set @firstname=null
if(@lastname='')
set @lastname=null
select emp_code,first_name,last_name,Department,designation from emp_main where first_name like isnull('%'+@firstname+'%','%') and last_name like isnull('%'+@lastname+'%','%') and Emp_code like isnull( '%'+@empcode+'%' , '%' )
END TRY
BEGIN CATCH
RAISERROR ('Retrieving Records Failed',16,1)
END CATCH
