CODEWITHSUNDEEP
sql-server
Pradees
Pradees

Reputation: 201

Execute stored procedure inside another stored procedure

I am executing another stored procedure from main stored procedure. Main stored procedure is given below 

    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<>''
                    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<>''
                    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

Second stored procedure is given below; it returns @HcpId fetching this @HcpId from main sp and trying to save from main sp. But it is not getting the @HcpId. How to achieve this? Is there any other solution for this?

    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

My issue is from below line in Main sp

IF @HospitalName IS NOT NULL AND @HospitalName<>''
                BEGIN
                    EXEC  @hcp_id= InsertPatAddedHosp @HospitalName, @Usr_Id 
                END

Upvotes: 1

Views: 20357

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

You should use an OUTPUT parameter for this. Here's a really simple example of how you can pass a mutable param from one proc to another:

CREATE PROCEDURE innerProcedure
@user_id INT, 
@hcp_id INT OUTPUT
AS 
BEGIN
    IF @user_id = 1
        SET @hcp_id = 100
    ELSE
        SET @hcp_id = 200
END
GO

CREATE PROCEDURE outerProcedure
@user_id INT
AS
BEGIN
    DECLARE @hcp_id INT
    EXEC innerProcedure @user_id, @hcp_id OUTPUT
    SELECT @hcp_id
END
GO

Try it out with params 1 and anything other than 1:

EXEC outerProcedure 1

Returns 100

EXEC outerProcedure 2

Returns 200

Note outerProcedure passes @hcp_id into innerProcedure, innerProcedure modifies its value with a SET operation, and then the value is passed back to outerProcedure. Note you need to include OUTPUT when you write the procedure that takes the param and anytime you call it.

I think you need to make these changes:

ALTER PROCEDURE [dbo].[InsertPatAddedHosp]
(
    @HospitalName nvarchar(100),
    @UserId nvarchar(100),
    @HcpId nvarchar(25) OUTPUT
)
As

Begin

    DECLARE @MaxHcpId int

    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)
        END
    ELSE
        BEGIN
            SELECT @HcpId = Hcp_Id FROM HCPPatAdded WHERE [User_Id]=@UserId AND Hcp_Name=@HospitalName
        END
End

And then, instead of this:

EXEC  @hcp_id= InsertPatAddedHosp @HospitalName, @Usr_Id

Call this:

EXEC  InsertPatAddedHosp @HospitalName, @Usr_Id, @hcp_id OUTPUT

Also, change the data type of @hcp_id to nvarchar(25) instead of varchar(25) to match the data type in InsertPatAddedHosp.

Upvotes: 7

Related Questions