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 |
|
leonr
Starting Member
15 Posts |
 Posted - 2012-04-05 : 04:29:18
|
Another question.... Is it bad practice to call an SP repetativly , maybe 10 times in a row? Or is it considered better practice to write a SP that can accept all the new values (same row) in one parameter and then do the update in one chunk? I currently do this in one SP, which accepts all the changes in 2 parameters... such as columns='col1,col2,col2' values='a,b,c' etc, however it would be SIMPLER to have the sp only accept one col and val parameter, and then call the SP multiple times from the client.Anybody have an preference to this? (as i say, im currently taking all the data in one hit, insert is a breeze, update is harder as the string has to be split on ',').Cheers! |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2012-04-05 : 09:53:51
|
| I can think of a few different conflicting answers and reasons for this Q... more info needed. OLTP? Size of table updated? Number of columns updated? Any predicates used? Source of data for the update?In general though I would avoid comma seperated strings, but that doesnt preclude the ability to call the SP once. |
 |
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-11 : 10:40:43
|
| Thanks for the reply!I agree, using comma seperated strings is making it overly complicated.Is there anything wrong with calling a SP (from a client app) multiple times (I mean like 10 times in a loop)? This makes it FAR easier to work with. I just tested it and it seems fine, but I was wondering more from a best practice point of view?At first it seemed efficent to send my update SP all of the data.. like...cols_to_update='[1],[2],[3]' and values ='a,b,c' etc, then the SP splits them and puts a in column 1, b in column 2.... but after doing this i'm starting to wonder if there is really anything bad about calling the SP for each update and do one at a time?Cheers! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-12 : 04:41:00
|
That's cool, thanks for this, I will read through it!Do you know still if it's bad practice to call a SP from a client app within a loop? (for example)10 updates = 10 SP calls, one after the other.I Can't see 'why' it would be a problem? There is the extra overhead of managing the calls from the client I guess?The array/table idea could change things though, as I guess technially I may not have to 'loop' within the stored procedure using this method.Cheers!quote:
Originally posted by visakh16
suggest you read thishttp://www.sommarskog.se/arrays-in-sql-2005.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
|
|
|
|
|
Dynamic Updates via SP