Database query with multiple parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database query with multiple parameters

Author

Topic

thewelshmike
Starting Member

2 Posts

Posted - 2011-04-19 : 09:41:57

Hello,
I'm quite new to SQL, and haven't got my head around what it can do, or how to do it.

(I'm using Visual studio 2010, with C# and SQL server 2008)

I'm working on a local restaurant directory website and an accompanying web service that is accessing an SQL database. I have a database of names, addresses, types of food, rating etc and some queries to select and insert data.

::On the website::
At the moment i have a drop down list with a list of towns, when selected, a Gridview displays what restaurants are in that town, this is just using WHERE name = @name in my dataset.

So i can have have all the restaurants in a certain town displayed. how do i refine this, adding another control, such as the type of food, that will mean the gridview displays all the chinese restaurants in that town, for example.
I also want it so if a user only selects one of the controls, it will still work, i.e it doesn't require all the parameters.

That might not make sense, like i say, I'm new to this, but any help would be great.
Cheers,
mike

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 11:49:46

I can think of two possible ways you could do this.

1. If the amount of data is not very large you could get all the data into your DataSet and then filter on the dataset. The advantage would be that you will reduce round-trips to the database server. The downside, of course, is that you don't want to do this if you have a few hundred thousand restaurants in your database.

2. Set up the query to the database to pass in the filter conditions you want to filter on and let database do the work. The general idea would be something like this, but.. see the big BUT below:


declare @cusine varchar(255);
declare @city varchar(255);

set @city = 'Stamford';
set @cusine = 'Indian';
select
   restaurant_name,
   street,
   zip_code
from
   restaurants
where
   (city = @city or @city is NULL)
   and (cusine = @cusine or @cusine is NULL)

Now the big "BUT" is that, while it is fine to test your theory using the query above, it is likely to perform poorly if you have a lot of restaurants in your database. So you need to do something different. Rather than explain how, let me give you these links:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://www.sommarskog.se/dyn-search-2005.html

thewelshmike
Starting Member

2 Posts

Posted - 2011-04-23 : 06:33:22

Hi,
Thanks for replying, great help, I'll let you know how I get on with i. I think getting the data then filtering might be the way to go, as my database will not be very large.

Thank you,
m

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources