Execute a Stored Procedure with parameters from a SELECT

Question

I have an SP which needs to be executed as many times as i find rows in a temporary table (Just like insertion using a select statement)

My table @OutMessageQIDs has id column (with say 10 rows) The SP HL7_InsertComponentLog requires 5 parameters as following

EXEC [HL7_InsertComponentLog] --@iPraID,@iComponentID,@vComponentType,'Send',id
SELECT @iPrcID,@iComponentID,@vComponentType,'Send',id
FROM @OutMessageQIDs

Can i do it without using Cursor or Loop kind of thing?

EDIT 1: a little more explanation - @OutMessageQIDs is a temporary table storing the ids of items Queued in a QueueTable (in an SP). and the SP HL7_InsertComponentLog logs the Queue-ed items. Depending upon @vComponentType, it logs different type of data.

EDIT 2: SP is as :

Create Procedure [dbo].[HL7_InsertComponentLog]
    @IPracID [int],
    @iComponentID [bigint],
    @vComponentType varchar(50),
    @vStatus varchar(200),
    @iOutMessageQueueID [bigint]
AS
    select* from hl7_outmessagelog

    IF @vStatus != 'Success'
    BEGIN
        SELECT -1
    END
    ELSE IF @vComponentType = 'LabOrder' OR @vComponentType = 'ProcedureOrder' OR @vComponentType = 'RadiologyOrder' 
    BEGIN 
        SELECT -1
    END
    ELSE IF @vComponentType = 'LabResult' OR @vComponentType = 'ProcedureResult' 

    BEGIN       
        INSERT INTO OrderResult_Addendum (iOrderDetailID,IUserID,DateTime_Stamp,iType,VchComments,iOrderID,iPracID,vAction,bAcknowledge)
                                SELECT   NULL,0,dTimeStamp,NULL,NULL,@iComponentID,@iPracID,@vStatus,0
                                FROM HL7_OutMessageQueue Q 
                                WHERE Q.iOutQueueID = @iOutMessageQueueID and iPracID = @iPracID
    END
    ELSE IF @vComponentType = 'RadiologyResult'
    BEGIN 
        INSERT INTO OrderResult_Addendum (iOrderDetailID,IUserID,DateTime_Stamp,iType,VchComments,iOrderID,iPracID,vAction,bAcknowledge)
                                SELECT   iOrderDetailID,0,Q.dTimeStamp,NULL,NULL,@iComponentID,@iPracID,@vStatus ,0
                                FROM HL7_OutMessageQueue Q 
                                INNER JOIN OrderResultDetails det ON Q.iComponentID = det.iOrderID
                                WHERE Q.iOutQueueID = @iOutMessageQueueID and Q.iPracID = @iPracID
    END
    ELSE IF @vComponentType = 'ClinicalNotes' 
    BEGIN       

        INSERT INTO Note_provider_encounter(iReportID,iUserID,iComponentID,dEncounterDate,vaction)
                                    SELECT  @iComponentID,0,0,dTimeStamp,@vStatus 
                                    FROM HL7_OutMessageLog Where iOutMessageLogID = @iOutMessageQueueID and iPracID = @iPracID
    END 
    ELSE IF @vComponentType = 'PatientDemo' 
    BEGIN       
        DECLARE @IPatID int
        DECLARE @IUserID int
        SELECT @IPatID = iPatID,@IUserID = iUserID
        FROM HL7_OutMessageQueue Q 
                                WHERE Q.iOutQueueID = @iOutMessageQueueID and iPracID = @iPracID
        EXEC [dbo].[InsertPatientLog] @IPracID,@IPatID,@vStatus,@IUserID
    END

Martin Smith · Accepted Answer · 2012-02-15 13:01:45Z

5

No you can't EXEC a stored procedure for each row in a table without using a loop of some kind.

What does your stored procedure do? It may be possible to extract the logic from that and perform it in a set based manner against the whole table.

e.g. Just use

INSERT INTO ComponentLog
SELECT @iPrcID,@iComponentID,@vComponentType,'Send',id
FROM @OutMessageQIDs

instead of calling the stored procedure for each row.

answered Feb 15, 2012 at 13:01

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Martin Smith Over a year ago

@Umer - An operation processing sets of rows rather than individual rows. Depends on what your stored proc does exactly whether or not this will be possible.

Umer Over a year ago

I've added some more explanation. can you review it ? i think i can't skip the SP execution as well because it compiles a long piece of information to log.

Martin Smith Over a year ago

@Umer - Without seeing the exact code impossible to tell. You could have a scalar UDF that compiles the information to log and call that in your SELECT statement instead.

Martin Smith Over a year ago

@Umer - It looks like it should definitely be possible to take the logic from that and do a version that deals with multiple rows. It will still need at least three different statements though for the insert into OrderResult_Addendum, Note_provider_encounter and PatientLog. How many rows are you expecting to have in your @OutMessageQIDs table? If it is only 10 as per the question and this code isn't frequently called it might not be worth the bother.

Martin Smith Over a year ago

@Umer - In 2008 you can use Table Valued Parameters to pass the table into the stored procedure itself. In 2005 this is not possible though you could create a #temp table outside the procedure and access it inside the procedure. This is obviously more fragile though as all the stored procedure callers need to be aware of this dependency. If you want to loop through the rows instead I'd just use a cursor rather than emulating one with a while loop.

|

Collectives™ on Stack Overflow

Execute a Stored Procedure with parameters from a SELECT

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related