stored procedure for filters

Question

i have a task @state varchar(50) @location varchar(50) as begin end if he pass state=location= then select all institutes in country if he pass state value then select institutes in that state if he pass location also value then select institutes in that particular location how to write query please help me

Tapan Patel · Answer

If you know about dynamic where clause  you can just make this SP to work with any parameter and all what you need to do is the pass either state or location or null and it works as expected. Just change your SP to this. CREATE  PROCEDURE  SPNAME @state  varchar (50) = NULL @location  varchar (50) = NULL as BEGIN SELECT  *  FROM  Your_Table_Name WHERE  state= COALESCE (@statestate)  AND location= COALESCE (@locationlocation) END what happens is when you pass state  coalesce function returns the first non null value that is state and when it comes to evaluating location it finds that location is not passed so it returns the location column for that record so the query looks like 'where location =location ' which is like 1=1 and it only returns the rows with value equal to the state you passed.   If you don't pass anything it will return all the records. Also if you have more table columns you can always write one stored procedure without any logic and it works.     Please mark it as an answer if it addresses the issue.

Santosh Sasupalli · Answer

thank u.. i know that but we have only two fields so we write select...... 2 times but we have 10 filters like that then ll we write 10 times. is it gud logic?  is any procedure just in single line querey for any number of filters

ketan borsdiya · Answer

HI Use below query CREATE PROCEDURE PRC_Test @state varchar(50) @location varchar(50) AS BEGIN SELECT * FROM institutes WHERE ([State] = @state or @state='') AND ([location]=@location or @location='') END If not getting solution then provide more details about your database

Midhun T P · Answer

Hi Please try like below - IF(@state='' AND @location ='') SELECT institutes FROM tablename ELSE IF(@state '' AND @location ='') SELECT institutes fROM tablename WHERE state=@state ELSE SELECT institutes fROM tablename WHERE location=@location