Tuesday, 2 February 2010

Using one stored procedure in another

Today I had to call a SPROC from another SPROC by sending it a parameter, storing the results in a temporary table, and then looping through the temp table. After some googling and tweaking I came up with this which seems to work a treat. I'm no SQL expert though!

--create temp table
 create table #temp (
    idx int identity(1,1),
    field1 int,
    field2 int)

 -- put data into temp table using other SP
 insert into #temp (field1, field2)
 exec myOtherSproc @paramaterToSend
 
 -- loop through temp table
 declare @counter int

 set @counter = 1

 while @counter < (select max(idx) from #temp)
  begin
   --do whatever you want here
   set @counter = @counter + 1
  end
 
 DROP TABLE #temp

No comments:

Post a comment

Comments are moderated, so you'll have to wait a little bit before they appear!