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 |
|
mtl777
Yak Posting Veteran
63 Posts |
 Posted - 2012-10-26 : 20:15:35
|
| Is there any way to call a stored procedure for each row of a query result set? Let's say you'd like to use the data of each row as the parameters for a stored procedure to be called. Is there any way to do that without having to use a cursor?Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-10-29 : 12:54:38
|
| Thanks a lot Visakh! Very cool techniques especially Apply.It would have been wonderful though if SQL Server could do exactly as I wanted. It's still not possible even with SQL Server 2012? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-29 : 13:40:09
|
| Normally you would write the stored procedure to operate on any number of rows. In other words, on sets of data, rather than one row/element of data. If you posted your code we might be able to demonstrate or rewrite it work that way. |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-10-29 : 21:48:24
|
| No application that I'm working on at the moment really. I was just wondering -- in case I needed to do something like it in the future. But one example comes to mind: Let's say you wanted to calculate the payroll for each employee in your company and the calculations are so complex, and furthermore, the results of the calculations are going to be inserted to a table and are going to be used to update other tables. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-30 : 09:50:25
|
quote:
Originally posted by mtl777
No application that I'm working on at the moment really. I was just wondering -- in case I needed to do something like it in the future. But one example comes to mind: Let's say you wanted to calculate the payroll for each employee in your company and the calculations are so complex, and furthermore, the results of the calculations are going to be inserted to a table and are going to be used to update other tables.
You can still write them in same procedure which accepts a list of employess. Then parse out list to temporary table to insert employee ids. then you can use set based technique using UPDATE to do required calculations and update them baack to any number of tables.And for individual employee payroll you can pass single value in list and it would still work fine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Calling a Stored Procedure for each row of a query