June 8, 2005 at 11:57 am
Hi All,
I'm writing a procedure to handle something that will happen on a Web site. Basically, the user is asked to select which items they want to add to a shopping cart. Each user may have selected up to 14 different items from a bank of about 120.
Three options I have thought of for this include:
1. Create a stored procedure with 14 input (int) params, insert these into a simulated array (if they're non-zero) and then loop over them.
2. Create a stored procedure with only one (int) param and call the stored procedure repeatedly from the Web code.
3. Create a bell-delimited string of non-zero IDs from the form input in the Web code, pass that string into SQL, and then perform necessary operations on that.
I'm leaning toward #3 because I think it will be the most extensible solution. This year the maximum number of inputs is 14, but next year it will almost definitely be different.
What do you think?
Cheers,
-m
June 8, 2005 at 12:00 pm
1 - Simply put : NO (what happens when you need 20 items)
2 - Too much overhead on the server/network
3 - Yes, have a look how to accomplish this task here :
June 8, 2005 at 2:01 pm
4. Create 14 different stored procedures, the first one with one parameter, next with two parameters etc. Call the correct one from client app.
5. Use parameterized queries from client app instead of stored procedures.
6. Store items in a temp table, then call stored procedure to insert them in one sweep to the 'real' table.
How is the items to be stored? As separate rows? If so, then I would probably opt for #5, building up an insert..select-statement using the selected items in the client app and executing that in a single execution.
June 8, 2005 at 2:05 pm
#5 is about the same as #3...
It depends only on the other params of the insert... if they are static, then I'd go with #3, else #5.
June 8, 2005 at 2:14 pm
Hi All,
Thanks for your input! I'm going with #3 because we already have some UDFs written here to work with this method.
Cheers,
-m
June 8, 2005 at 2:25 pm
The thing that I am constantly puzzled by is why everyone is so happy to do dynamic parameters (if by nothing else then at least using (faked) arrays and lists) in SQL Server stored procedures, but can not even start to think about building dynamic statements on the client side. For every question like "I want to be able to supply X parameters to my stored procedure but not always supplying a value for each", I just want to counter with "Why?".
Naturally it is better sometimes to handle dynamic params in a proc, but normally .Net (or any other modern client environment) is so much easier to work with for handling this.
June 8, 2005 at 2:31 pm
Well mostly because it avoids dynamic sql altogether. Also it avoids doing 14 proc calls lessening the load on the server and on the network.
June 8, 2005 at 2:44 pm
I have to admit, I'm a disciple of Erland Sommarskog's teachings, that's why I was leaning toward #3 in the first place. I admit that it's partially because of familiarity with this method.
Chris, would you be willing to post a quick example using paramaterized queries?
Cheers,
-m
June 8, 2005 at 2:47 pm
I'm wondering if he's talking about using sp_executesql for this??.. we'll see.
June 8, 2005 at 3:39 pm
Argh. Just lost a quite long post on this.. What it more or less said was just that I feel that often people tend to write as-simple-as-possible "client-side" data access code (like simply executing a stored procedure with a single parameter, which in turn actually contains several parameters concatenated into a string), then write a procedure that is not as-simple-as-possible (for instance using smart ways to split the string into individual parameters and doing something with them). Sure, that is the right way to go in many cases, but in others it might be much easier to simply let the data access code build a complete sql statement and just let the dbms execute it.
June 8, 2005 at 4:42 pm
I see what you mean. I was reffering to something like : Select * from MyTable where MyId in (Select Element from dbo.Split(@Param, ',')), while you seem to be reffereing to a complexe search with tons of parameters. While I wrote those kind of uselessly complex stored procs, I tend to agree that this could be better handled at the client side. The only thing I don't like about this is the risk of sql injection. But that too can be contained with good validation...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply