主存储过程内调用其他存储过程技术咨询（附主存储过程代码）

To execute another stored procedure within SaveLabTestEntry, you can use the EXEC (or EXECUTE) command directly within your BEGIN...END block. Here's how to adjust your existing procedure to include this call, along with key considerations:

Modified Procedure Code #

ALTER Procedure [dbo].[SaveLabTestEntry] 
( 
    @LE_No nvarchar(100)=null, 
    @hcp_id varchar(25)=null, 
    @Pat_prof_Id nvarchar(100)=null, 
    @Usr_Id nvarchar(100)=null, 
    @LE_Date nvarchar(100)=null, 
    @LE_Time nvarchar(100)=null, 
    @HospitalName nvarchar(100)=null, 
    @Mode nvarchar(10)=null, 
    @dt LabEntryD_TVP READONLY 
) 
AS 
Declare @ResultMsg as nvarchar(50) 
Declare @MaxID as Int 
Declare @Status as Int 
Begin 
    -- Your existing pre-call logic here (e.g., inserting into main tables, setting variables)
    
    -- Call your target stored procedure (replace with actual name/parameters)
    EXEC dbo.TargetStoredProcedure 
        @PatientID = @Pat_prof_Id, 
        @UserID = @Usr_Id, 
        @EntryDate = @LE_Date;
    
    -- Your existing post-call logic here (e.g., updating status, returning messages)
    SET @ResultMsg = 'Lab entry saved successfully';
    SELECT @ResultMsg AS ResultMessage;
End

Important Details to Keep in Mind #

• Parameter Matching: Ensure the parameters you pass to the called procedure match its definition (name, data type, and order if you're not using named parameters).
• Capturing Results: If the called procedure returns a result set, you can capture it using a temporary table or table variable:

  -- Create a temp table to hold the output
  CREATE TABLE #ProcedureResults (ID INT, Status NVARCHAR(50));
  
  -- Insert results into the temp table
  INSERT INTO #ProcedureResults
  EXEC dbo.TargetStoredProcedure @PatientID = @Pat_prof_Id;
  

• Transaction Management: If your main procedure uses transactions, be cautious with nested transactions in the called procedure. SQL Server treats nested transactions as a single transaction, so a COMMIT in the called procedure won't finalize the entire transaction until the main procedure commits.
• Error Handling: Add TRY...CATCH blocks if you need to handle errors from the called procedure gracefully within the main procedure.

Content of the question originates from Stack Exchange, question author Pradees