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 |
|
Kristen
Test
22859 Posts |
 Posted - 2006-11-07 : 10:39:58
|
| I don't do this very often, but now I have a situation which is time critical.I have some rows in, say, #TEMP. The columns basically map on to the parameters in an Sproc. The SProc does a bit of Business Rule checking, and then Inserts the row.I could Insert the row directly, but then I am at risk from Business Rule changes in the future.Is there any perceived wisdom on how to handle this?I reckon my choices are:SELECT TOP 1 @Param1 = Col1, @Param2 = col2, ...FROM MyTableWHERE MyItem = @NextItemEXEC MySProc @Param1 = @Param1, @Param2 = @Param2, ...or I could call MySProc with a #TEMP table and change the behaviour of MySProc to be capable of walking round a table and doing its internal logic on each row.Either way this looks desperately slow compared to:INSERT INTO MyTargetTable SELECT * FROM #TEMPso I am hoping for some Hints and Tips please!Kristen |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-07 : 10:45:46
|
| Bot-Off! |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-07 : 11:46:34
|
i'm being serious.  so you have to do a row by row processing of all rows in #temp table?i'm not quite clear on what you're asking...Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-11-07 : 12:25:56
|
| >> The SProc does a bit of Business Rule checking, and then Inserts the row.>> change the behaviour of MySProc to be capable of walking round a table and doing its internal logic on each row.That's why it would be slow.You need to change the sp to work on sets. Then it can deal with the temp table - maybe processing batches of rows for performance/locking.Quite often I will process a table by having a control SP which extracts a batch of rows (or ids) into a temp table then calls another sp to process the rows in that sp. In that way the control sp can be changes to select the number of rows the other sp can process most efficiently.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-07 : 12:33:08
|
"i'm being serious"Oh  Sorry about that.I have a Shopping Basket that the user has abandoned. I want to offer them to reload it next time they visit the store.The shopping basket has Items table (Product + Qty etc), Delivery table (so that Qty can be split into multiple deliveries), and then for each Delivery an Options table - to store the choices they make - like how many shelves in their Freezer.When they next login they may have already started to fill their basket ... so I have to take decisions about reloading the basket based on whether they've already put that product in their basket, or if the product is no longer sold, etc.So the Item Numbers from the original basket bear no relation to the item numbers in the new basket :(So for each Item in the basket I want to call an AddToBasket Sproc. And for each of the original Delivery table entries I then want to call the CreateDelivery Sproc, and Ditto for the Options in a Delivery.It seems very long winded to doSELECT @Param1 = Col1, @Param2 = Col2, ... FROM #TEMPEXEC AddTobasket @Param1 = @Param1, @Param2 = @Param2because I must pre-declare all the @Param1... variables which have no use in this Sproc other than to transport data from #TEMP to the EXEC AddTobasket Sproc.I must also make sure that I keep them up-to-date if the structure of the AddTobBasket SProc changesSo all-in-all it seems a bit crap, and I was hoping that someone had a suggestion of a better way.I've even being considering dynamic SQL as being easier than pre-creating all the @Param local variables:SELECT @strSQL = 'EXEC AddToBasket @param1=''' + col1 + ''', @param2=''' + col2 + ''', ...'FROM #TEMPthat's the sort of sad state I've talked myself into! which will probably lead to me trying the undocumented xp_execresultset as a solution ...Kristen |
|
|
|
|
|
|
|
|
Best way to run EXEC based on rows in #TEMP?