Sql-server – How to pass output of one stored procedure to the input of another

sql server

I have a stored procedure which outputs @billingInvoiceIDOut. I want to use it as input in my next stored procedure. Can I just do in my second stored procedure

INSERT INTO table ( billingID ) VALUE (@billingidout)

My first stored procedure:

BEGIN
    BEGIN TRANSACTION 
    BEGIN TRY
        SET NOCOUNT ON;
        IF NOT EXISTS(SELECT billingInvoiceID 
                      FROM tblBillingInvoice 
                      WHERE billingInvoiceID = @billinginvoiceID)
        --  IF @billinginvoiceID = NULL
        BEGIN
            INSERT INTO [dbo].[tblBillingInvoice
                (invoicenumber,versionnumber,patientID,date,
                officeID,userID,[tblBillingInvoice].status)
            VALUE ((SELECT IDENT_CURRENT('tblbillingInvoice')),1,@patientID,GETDATE(),
                    @officeID,@userID,1)
            SET @billingInvoiceIDOut =  (SELECT IDENT_CURRENT('tblbillingInvoice')) 
        END
    END TRY

or do I have to pass this parameter in exec statement as input, as I am running both stored procedures in one master stored procedure?

Best Answer

You must pass the value from the first SP to the second via a variable local to the master SP. The bare bones of the code will look something like this:

create master_sp
..
declare @LocalVariable int;

exec first_sp
    @sp_parameter = @LocalVariable OUTPUT;

exec second_sp
    @sp_input = @LocalVariable;

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Sql-server – Oracle GoldenGate add trandata errors

Related Question