Select Results Execute a Procedure

[MadBnz]

Hi,
I am trying to write a Procedure that will return a set of ID's and then Execute another procedure based on the results

Basically I have a working procedure that archives files associated with a single Item_ID, so users can archive on a per Item basis, but I also require someway of processing all records that match an Expire Date.

I have another Procedure that can select these Expired Items and would like this to sequentially run the Archive procedure.

eg.
Execute ProcedureName Item_ID for Each Item_ID In
( Select Item_ID from Table where Expire_Date < Getdate() )

Clear as mud?

 

[jbenson001]

I guess you can first select your 1st procedure's IDs into a temp table. Then run your second stored procedure against that table. Then drop the temp table.

Jim

 

[MadBnz]

Found an answer using a cursor... is there a better way?
BTW the Stored Proc sp_Do_Ad_Archive performs the Archive action on a per Ad basis

ALTER  Procedure sp_Do_Archive_Run_Ads
As
declare @AdID int
declare loop_Archive cursor for
Select 
	 Ad_ID
From
	 dbo.tbl_Ad,dbo.tbl_Edition
Where
	Ed_ID = Ad_Ed_ID
And	Ed_Date < GetDate()
Open loop_Archive
Fetch Next From loop_Archive Into @AdID
While @@fetch_status = 0
Begin
	Exec sp_Do_Ad_Archive @AdID
	Fetch Next From loop_Archive Into @AdID
End
Close loop_Archive
deallocate loop_Archive

 

[SQLSister]

Cursors evil, to be avoided if possible as they are slow and will gum up your system performance. The best way is to put all your code in one stored procedure and run set-based statements.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief