RE: How to use results from a stored procedure within a stored procedure?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: cbretana (anonymous_at_discussions.microsoft.com)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 16:21:03 -0700

If you're using SQL 7 or later, you can reqrite storedProc2 as a User Defined FUnction that returns a Table variable. And then use that table variable just like you would use any other Table. This might be the best option if the Tabkle storedProc2 generates is small (not many rows) and narrow, Like just Primary Keys.)) Remember since you can use this rtable variable in StoredProc1 just like a table, you cvan join it to the other real Tables fopr any other data you need. I often uise the following construction in a Stored Proc

Declare @Tab Table (myPK Integer Primary Key Not Null)
Declare @pk Integer
Set @Tab = dbo.FunctionReturningPKValuesinTable(Paramaters)

While Exists (Select * From @Tab)
     Begin
          Select @pk = Max(myPK) From @Tab
          /* **************
          Do Stuff with @pk
          ****************/
          Delete @Tab Where myPK = @pk
     End