By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Loop on results, call sproc

jkmyoung
Expert 100+
P: 2,057
Hi,
I'm looking for a way to loop on a set of temporary results and run a stored procedure on each row of the results.

Simplified code of what I'm trying to do:
Expand|Select|Wrap|Line Numbers
  1. Declare @TMP TABLE
  2. (
  3. field1 Int
  4. field2 Int
  5. )
  6.  
  7. INSERT INTO @TMP
  8. SELECT id, otherVar
  9. from ....blah join blah join blah
  10. where bar = foo
  11.  
  12.  
  13. Declare @RC = int
  14. -- Want to loop here.
  15. For each result.
  16. Exec @RC = [Process].[dbo].[usp.storedProc1]   **field1** **field2**
  17. End for
  18.  
What is the syntax to do this? Also is this terminology correct, or is there another specific word for what I'm looking for?
Sep 18 '08 #1
Share this Question
Share on Google+
1 Reply


jkmyoung
Expert 100+
P: 2,057
Finally managed to find a solution:
Expand|Select|Wrap|Line Numbers
  1. Declare @TMP TABLE
  2. (
  3. field1 Int
  4. field2 Int
  5. )
  6.  
  7. INSERT INTO @TMP
  8. SELECT id, otherVar
  9. from ....blah join blah join blah
  10. where bar = foo
  11.  
  12.  
  13. Declare @RC = int
  14. Declare @f1 = int
  15. Declare @f2 = int
  16. declare cur_1 cursor for select field1, field2 from @TMP
  17.  
  18. -- like a do-while
  19. fetch next from cur_1 into @f1, @f2;
  20. while @@FETCH_STATUS = 0
  21. begin
  22.     Exec @RC = [Process].[dbo].[usp.storedProc1]   @f1, @f2
  23.     fetch next from cur_1 into @f1, @f2;
  24. end 
  25. close cur_1;
  26.  
Sep 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.