Search by different parameters of stored procedure SQL Server

If you want to provide your user searching facility in such a way that he can search with any of the combination of field then to achieve this objective a stored procedure can be created, which will have parameters (all the columns of your search criteria) which could implement search in different ways

Here in this article, we have presented two ways to achieve this; you can have few more way to achieve the same.  For explanation we are creating a table Tab_VKM_Search which has five column and we will be providing user search on first_name, last_name and phone number.   

Create table Tab_VKM_Search

(

emp_id int identity(1,1) primary key,

first_name varchar(20),

last_name varchar(10),

email_id varchar(20),

phone varchar(10))

Here our table is ready, let’s insert few records in it and make a ground for our stored procedure.

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Rakesh','Sharma','rak@abo.com','982222222')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Rajesh','Mehra','raj@abo.com','982222223')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Romesh','Sippy','rom@abo.com','982222224')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Prakash','Padukone','prakesh@abo.com','982222225')

insert into Tab_VKM_Search(first_name,last_name,email_id,phone) values('Prabhu','Chavla','pra@abo.com','982222226')

FIRST Approach (dynamic query)

Here with the help of dynamic query we are trying to provide the solution.

--PROC_Search_Employee 'pr','s'

alter procedure PROC_Search_Employee

@first_name varchar(20) = NULL,

@last_name varchar(10) = NULL,

@phone varchar(10) =NULL

as

declare @query varchar(max);

begin

      set nocount on;

      SET @query = 'select  *

              FROM Tab_VKM_Search

              WHERE 1=1 '

      IF @first_name IS NOT NULL

       SET @query = @query + ' AND first_name like '''+@first_name+'%'''

      IF @last_name IS NOT NULL

       SET @query = @query + ' AND last_name like '''+@last_name +'%''' 

      IF @phone IS NOT NULL

             SET @query = @query + ' AND phone like '''+@phone +'%'''

     

      exec(@query)

end

Second Approach (with coalesce)

Here we are using Coalesce function to come up with our solution.

--PROC_Search_Employee_II 'p','s'

alter procedure PROC_Search_Employee_II

@first_name varchar(20) = NULL,

@last_name varchar(10) = NULL,

@phone varchar(10) =NULL

as

begin

      set nocount on;

      select  * from Tab_VKM_Search  

    WHERE first_name like coalesce(@first_name + '%',first_name)

      and last_name like coalesce(@last_name + '%',last_name)

      and phone like coalesce(@phone + '%',phone)                

end

NOTE: Any of the approach you can use depends on your IO cost in the explain plan.

If you are a PROGRAMER only by CODE you build yourself...

Post Reference: Vikram Aristocratic Elfin Share