CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Umer
Umer

Reputation: 1921

Execute a Stored Procedure with parameters from a SELECT

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

Upvotes: 2

Views: 12193

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

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.

Upvotes: 5

Related Questions