Selecting Data from table with multiple conditions on multiple columns

Question

Selecting Data from table with multiple conditions on multiple columns

surya.mekala

Newbie

Points: 6
More actions
April 15, 2014 at 11:17 pm

#396610

Hi,
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Kindly help me to sort out the problem.
Regards
Surya

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

It can be done in dynamic sql but 1st i need to know, is there any limit of there will always be Price1, Price2 & Price3 etc columns, or you can have N number of Price ? and other related columns

surya.mekala Newbie Points: 6 More actions · Answer 2

Yeah the limit is upto 10 columns.

And dynamic SQL ? Could you please guide me in writing that query in dynamic sql

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

surya.mekala (4/16/2014)
Yeah the limit is upto 10 columns.
And dynamic SQL ? Could you please guide me in writing that query in dynamic sql

Ok, as per your answer you donot need to use dynamic sql. you can easily do it wil a User-Define Table Type. Read the following article how to use the User-Define Table Type as a parameter in stored procedure.

http://queriesinsql.blogspot.com/2013/02/bulk-insert-into-table-using-user.html

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Always start simple:

SELECT *

FROM Car

WHERE Price IN (@Price1,@price2,@price3)

AND Manufacture IN (@Manufacture1,@Manufacture2,@Manufacture3)

AND Model IN (@Model1,@Model2,@Model3)

AND City IN (@City1,@City2,@City3)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden