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.
| Author |
Topic |
|
BigSam
Starting Member
30 Posts |
 Posted - 2005-12-01 : 16:34:22
|
I need a query to locate a customer. The web page will allow, but not require the user to enter in various pieces of information: lastname, firstname, city, state, zip,...I can build the select statement on the fly in the web page, but I'm told that I really want to use a stored procedure. Rather than have oodles of stored procedures to cover every possibility of input field, how can I build one that will dynamically filter the selection criteria?Maybe a better question is, what is the best way to query the database to support veriable # of where clause values? BigSam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-01 : 16:43:14
|
| There is no point in using stored procedures if you want to design them this way. You'd be better off building the queries in your application and then using inline sql.Tara Kizeraka tduggan |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-02 : 00:23:42
|
| If you want to allow the user to write queries, there are chances Delete, Drop can be used. Be aware on thatMadhivananFailing to plan is Planning to fail |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-02 : 08:36:34
|
| 1. I would second Tara's idea that if you are going to build a dynamic statement anyway that you would be just as well off just building the query inline.2. Your stored procedure doesn't have to have a variable # of where clauses. You could pass all of the values to it (NULL for parameters not set) and then simply have the followingFROM tablewhere (@LastName IS NULL or customer.lastname = @lastname)and (@City IS NULL or customer.city = @city)and (@Zip IS NULL or customer.zip = @zip)etc. Upside being that you still have one precompiled select clause. The downside being that the performance will be awful for it, because each time it is executed it would need different indexes, but the execution plan probably wouldn't have it correct. |
|
|
|
BigSam
Starting Member
30 Posts |
Posted - 2005-12-02 : 08:59:33
|
| Thanks. At worst the query will be Select fields from table.I understand the dangers of the delete, which was why I was trying to use a stored procedure. I suppose another option is to do the filerting in my programming after the select & prior to the display. Since this is an intranet application that may have some benefit.BigSam |
|
|
|
|
|
|
|
|
Query in ASP