stored procedure for filters

Santosh Sasupalli

10y776

i have a task

@state varchar(50),

@location varchar(50)

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel10y ago
Accepted 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(@state,state) AND
location=COALESCE(@location,location)
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 Sasupalli10y ago
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 borsdiya10y ago
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 Tp10y ago
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