title: Execute stored procedure inside another stored procedure
body: <p>I am executing another stored procedure from main stored procedure. Main stored procedure is given below  </p>
  
  <pre><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
      BEGIN TRY
          BEGIN TRANSACTION SaveData
          IF @Mode='ADD'
              BEGIN           
  
                  IF @HospitalName IS NOT NULL AND @HospitalName&lt;&gt;''
                      BEGIN
                          EXEC  @hcp_id= InsertPatAddedHosp @HospitalName, @Usr_Id 
                      END             
  
                  SET @MaxID = (SELECT [Vr_No] + 1 FROM [VrLastNo] WHERE [Vr_Code]='LAB-H' AND [Loc_Id]=1 AND [Co_Cd]='HO')
                  SET @LE_No = 'P'+CAST(@MaxID AS NVARCHAR(100))
                  INSERT  INTO HCPLabEntryH(LE_No,    hcp_id,     Pat_prof_Id,    Usr_Id,     LE_Date,    LE_Time,    Entry_Type)
                  VALUES                   (@LE_No,   @hcp_id,    @Pat_prof_Id,   @Usr_Id,    @LE_Date,   @LE_Time,   'P')
  
                  INSERT INTO HCPLabEntryD(
                              hcp_id,             Pat_prof_Id,            LE_No,      LED_Ctr,        Test_Cd,    Test_Desc,              
                              LED_Result,         LED_Normal_Value)                           
                  SELECT  
                              @hcp_id,            @Pat_prof_Id,           @LE_No,     tvp.LabTestCtr, tvp.TestCd, tvp.TestName,
                              tvp.TestResult,     tvp.NormalValue                         
  
                  FROM @dt tvp 
  
                  SET @ResultMsg='Saved Successfully..'
                  Set @Status=1
  
                  UPDATE [VrLastNo] SET [Vr_No]= @MaxID WHERE [Vr_Code]='LAB-H' AND [Loc_Id]=1 AND [Co_Cd]='HO'           
              END
          IF @Mode='EDIT'
              BEGIN
  
                  IF @HospitalName IS NOT NULL AND @HospitalName&lt;&gt;''
                      BEGIN
                          EXEC  @hcp_id= InsertPatAddedHosp @HospitalName, @Usr_Id
                      END     
  
                  UPDATE HCPLabEntryH SET 
                          hcp_id=@hcp_id,     Pat_prof_Id=@Pat_prof_Id,   Usr_Id=@Usr_Id,     LE_Date=@LE_Date,   LE_Time=@LE_Time
                  WHERE LE_No=@LE_No
  
                  DELETE FROM HCPLabEntryD WHERE LE_No=@LE_No
  
                  INSERT INTO HCPLabEntryD(
                              hcp_id,             Pat_prof_Id,            LE_No,      LED_Ctr,        Test_Cd,    Test_Desc,              
                              LED_Result,         LED_Normal_Value)                           
                  SELECT  
                              @hcp_id,            @Pat_prof_Id,           @LE_No,     tvp.LabTestCtr, tvp.TestCd, tvp.TestName,
                              tvp.TestResult,     tvp.NormalValue                         
  
                  FROM @dt tvp 
  
                  Set @Status=1
                  SET @ResultMsg='Updated Successfully..'
              END
          IF @Mode='DELETE'
              BEGIN
                  DELETE FROM HCPLabEntryH WHERE LE_No=@LE_No
                  DELETE FROM HCPLabEntryD WHERE LE_No=@LE_No
  
                  Set @Status=1
                  SET @ResultMsg='Deleted Successfully..'
              END
      SELECT @ResultMsg as ResultMsg,@Status as [Status],0 as Id
      COMMIT TRANSACTION SaveData
  END TRY
  BEGIN CATCH
      rollback transaction SaveData;
      Set @Status=0
      select Error_Message() as ResultMsg,@Status as [Status],0 as Id
  END CATCH 
  End
  </code></pre>
  
  <p>Second stored procedure is given below; it returns <code>@HcpId</code> fetching this <code>@HcpId</code> from main sp and trying to save from  main sp. But it is not getting the <code>@HcpId</code>. How to achieve this? Is there any other solution for this?</p>
  
  <pre><code>    ALTER PROCEDURE [dbo].[InsertPatAddedHosp]
      (
      @HospitalName nvarchar(100),
      @UserId nvarchar(100)
  )
  As
  
  Begin
  
      DECLARE @MaxHcpId int
      DECLARE @HcpId nvarchar(25)
  
      IF NOT EXISTS (SELECT Hcp_Name FROM HCPPatAdded WHERE [User_Id]=@UserId AND Hcp_Name=@HospitalName)
          BEGIN
              SET @MaxHcpId= (SELECT ISNULL((SELECT ISNULL([VALUE],0) FROM [Param] WHERE [KEY]='PatAddedHosId'),0))
              SET @HcpId='H'+CAST((@MaxHcpId+1) AS NVARCHAR(100))
  
              IF @MaxHcpId = 0
                  BEGIN
                      INSERT INTO [Param]([KEY], [YEAR], [VALUE]) VALUES ('PatAddedHosId', -1, 1)
                  END
              ELSE
                  BEGIN
                      UPDATE [Param] SET [VALUE]=@MaxHcpId+1 WHERE [KEY]='PatAddedHosId'
                  END
  
              INSERT INTO HCPPatAdded(Hcp_Id, Hcp_Name, [User_Id]) VALUES(@HcpId, @HospitalName, @UserId)
  
              SELECT @HcpId
          END
      ELSE
          BEGIN
              SELECT Hcp_Id FROM HCPPatAdded WHERE [User_Id]=@UserId AND Hcp_Name=@HospitalName
          END
  End
  </code></pre>
  
  <p>My issue is from below line in Main sp</p>
  
  <pre><code>IF @HospitalName IS NOT NULL AND @HospitalName&lt;&gt;''
                  BEGIN
                      EXEC  @hcp_id= InsertPatAddedHosp @HospitalName, @Usr_Id 
                  END   
  </code></pre>