title: Execute a Stored Procedure with parameters from a SELECT
body: <p>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)</p>
  
  <p>My table <code>@OutMessageQIDs</code> has <code>id</code> column (with say 10 rows)
  The SP <code>HL7_InsertComponentLog</code> requires 5 parameters as following </p>
  
  <pre><code>EXEC [HL7_InsertComponentLog] --@iPraID,@iComponentID,@vComponentType,'Send',id
  SELECT @iPrcID,@iComponentID,@vComponentType,'Send',id
  FROM @OutMessageQIDs
  </code></pre>
  
  <p>Can i do it <strong>without</strong> using <code>Cursor</code> or <code>Loop</code> kind of thing? </p>
  
  <p><strong>EDIT 1:</strong> a little more explanation - @OutMessageQIDs is a temporary table storing the ids of items Queued in a QueueTable (in an SP). and the SP <code>HL7_InsertComponentLog</code> logs the Queue-ed items. Depending upon @vComponentType, it logs different type of data.</p>
  
  <p><strong>EDIT 2:</strong> SP is as :</p>
  
  <pre><code>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
  </code></pre>