Looping through a temp table to exec Store Proc

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Looping through a temp table to exec Store Proc

Author

Topic

asn187
Starting Member

9 Posts

Posted - 2007-04-25 : 08:37:51

I am running the query below to populate a temporary table with some data as below.

CREATE TABLE #memberSchoolRows (memberID uniqueidentifier, orgID int)
INSERT INTO #memberSchoolRows (memberID, orgID)
SELECT [dbo].org_member.member_id, [dbo].org.org_id
FROM [dbo].org
INNER JOIN [dbo].org_member
ON [dbo].org_member.org_id = [dbo].org.org_id
WHERE [dbo].org.org_id = 'A49409DB-2142-46B7-AD77-54D1B56BCB06'

I then want to execute a stored procedure for each row in the temporary table using the data in the two columns of the temp table

exec sp_Populate @memberID, @orgID

However, how would i loop through the temporary table and then executing the stored proc for each row in the temp table?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 08:53:03

It would be better if you could re-write your SP so that it can work on entire temp table at once rather than looping through each record in temp table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

asn187
Starting Member

9 Posts

Posted - 2007-04-25 : 08:59:56

this wont be possible as it deals with many updates and inserts etc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 09:04:22

quote:
Originally posted by asn187

this wont be possible as it deals with many updates and inserts etc

Impossible or just need more work ?

asn187
Starting Member

9 Posts

Posted - 2007-04-25 : 09:17:15

think i may have solved it...
think the below should work?

CREATE TABLE #memberSchoolRows (memberID uniqueidentifier, orgID int)
INSERT INTO #memberSchoolRows (memberID, orgID)
SELECT [dbo].org_member.member_id, [dbo].org.org_id
FROM [dbo].org
INNER JOIN [dbo].org_member
ON [dbo].org_member.org_id = [dbo].org.org_id
WHERE [dbo].org.org_id = 'A49409DB-2142-46B7-AD77-54D1B56BCB06'

-- declare a cursor to loop through the temp table
-- Declare the variables to store the values returned by FETCH.
DECLARE @memberID uniqueidentifier, @orgID int

DECLARE schools_cursor CURSOR FOR
SELECT * FROM #memberSchoolRows

OPEN schools_cursor

-- do first fetch and store the values in vars.
print 'opened cursor'

FETCH NEXT FROM schools_cursor
INTO @memberID, @orgID

-- check @@FETCH_STATUS see more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

-- run the stored procedure here to populate the group with these memebr

-- get next
FETCH NEXT FROM schools_cursor
INTO @memberID, @orgID
END

CLOSE schools_cursor
DEALLOCATE schools_cursor
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 09:25:07

Yikes!! That's cursor there...not the best possible solution.

If you can't modify your SP, then add an identity column to the temp table and use a WHILE loop to loop through each record and call SP.

Declare @i int, @max int

Select @i = 1, @max = max(id) from #memberSchoolRows 

While @i <= @max
begin
   Select @memberID = memberID, @orgID = OrgID
   From  #memberSchoolRows where ID = @i

   Exec sp_Populate @memberID, @orgID

   Set @i = @i + 1
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources