4

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?

1

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;
0

Use the new ID as a parameter to the second SP. However, you can set the ID as an OUTPUT ofbtge first SP.

1
  • A code example of what you mean would help a lot here. – Colin 't Hart Nov 25 '14 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.