CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
6dev6il6
6dev6il6

Reputation: 857

How to EXEC a stored procedure using values from a table

I have a proc that retrieves data from a table based on some conditions and inserts that into a temp table.

I now want to execute another proc using the values from my temp table, which will have about 2000 entries.

Can I do this in a single EXEC and SELECT all the entries from my #Temp table?

I want to do something like this:

DECLARE @return_value int
EXEC    @return_value = [dbo].[prInsertAssessmentLevels]
        @WFRouteItemID = (SELECT WFRouteItemID FROM #WFsNotInWFAssessmentLevel),
        @UOB = (SELECT UOB FROM #WFsNotInWFAssessmentLevel WHERE WFRouteItemID = @WFRouteItemID),
        @Life = (SELECT Life FROM #WFsNotInWFAssessmentLevel WHERE WFRouteItemID = @WFRouteItemID),
        @Health = (SELECT Health FROM #WFsNotInWFAssessmentLevel WHERE WFRouteItemID = @WFRouteItemID),
        @Disability = (SELECT Disability FROM #WFsNotInWFAssessmentLevel WHERE WFRouteItemID = @WFRouteItemID),
        @CreatedUserID = 1

Upvotes: 1

Views: 114

Answers (2)

Tom Page
Tom Page

Reputation: 1241

You'll need a cursor

DECLARE @return_value int
DECLARE @WFRouteItemID  int /* Or whatever the correct Type is*/
DECLARE @UOB            int /* Or whatever the correct Type is*/
DECLARE @Life           int /* Or whatever the correct Type is*/
DECLARE @Health         int /* Or whatever the correct Type is*/
DECLARE @Disability     int /* Or whatever the correct Type is*/
DECLARE @CreatedUserID  int /* Or whatever the correct Type is*/

DECLARE PARAM_CURSOR CURSOR FOR 
SELECT WFRouteItemID, UOB, Life, Health, Disability, CreatedUserID
FROM #WFsNotInWFAssessmentLevel 


OPEN PARAM_CURSOR 
FETCH NEXT FROM PARAM_CURSOR  INTO @WFRouteItemID, @UOB, @Life, @Health, @Disability, @CreatedUserID
WHILE @@FETCHSTATUS = 0
BEGIN

EXEC    @return_value = [dbo].[prInsertAssessmentLevels],@WFRouteItemID, @UOB, @Life, @Health, @Disability, @CreatedUserID     

FETCH NEXT FROM PARAM_CURSOR  INTO @WFRouteItemID, @UOB, @Life, @Health, @Disability, @CreatedUserID
END
CLOSE PARAM_CURSOR 
DEALLOCATE PARAM_CURSOR

Upvotes: 1

Brett Schneider
Brett Schneider

Reputation: 4103

what you want to do is more like going through your table row-by-row and executing the stored procedure with all the row values. let me give you a template for a cursor-based approach:

DECLARE curName CURSOR FOR 
      SELECT col1, col2, col3 
      FROM your_table

OPEN curName
      FETCH NEXT FROM curName INTO @pk
      WHILE @@FETCH_STATUS = 0
      BEGIN
            EXEC your_Procedure (@par1 = @col1, @par2 = @col2, @par3 = @col3)
            FETCH NEXT FROM curName INTO @col1, @col2, @col3
      END
CLOSE curName

DEALLOCATE curName

Upvotes: 1

Related Questions