A procedure to filter data on multiple conditions with several parameters.

This proc takes 6 parameters, all parameters allow for null values, it will returns the result whether you pass null or any parameter.

• If you pass one parameter and rest of null then it will filter on single parameter.
• If you pass 2 parameters and rest of null then it will filter result on 2 parameters.
• If you pass all parameters then it will filter result on all parameters.
• If you do not pass any parameter then It will returns all the records from the table.

create  proc [dbo].[usp_GetData]
@name varchar(100)= NULL ,
@sector varchar(50)= NULL ,
@pocket varchar(50) =NULL ,
@plot varchar(50)= NULL  ,
@locality varchar(50) =NULL,
@department varchar(50) =null
as

 Select * from m_Details where
 ((v_RegName like '%'+@name+'%' or @name is  null) and (v_Department=@department or @department is  null))
 and ((n_sector =@sector or @sector is null) and (v_Department=@department or @department is  null))
 AND ((v_pocket=@pocket or @pocket is null) and (v_Department=@department or @department is  null))
 AND ((n_plotNo=@plot or @plot is  null) and (v_Department=@department or @department is  null))
 AND ((v_locality =@locality or @locality is null) and (v_Department=@department or @department is  null))