Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Norway
    Posts
    6

    Unanswered: How do I use OUTPUT in a nested transaction?

    I want to take the value from an autonumbered field, and put that value into another table.
    Example:

    CREATE PROCEDURE add_customer
    @FirstName varchar(50),
    @LastName varchar(60),
    @CompanyName varchar(100),
    @EMail varchar(100),
    @Address varchar(100),
    @City varchar(100),
    @State varchar(100),
    @PostalCode varchar(20),
    @Country varchar(50),
    @CustomerID int OUTPUT

    AS
    BEGIN TRAN Add_Customer1
    INSERT INTO Customer
    (FirstName, LastName,EMail)
    VALUES
    (@FirstName, @LastName, @EMail)
    SELECT @CustomerID = CustomerID FROM Customer
    COMMIT TRAN Add_Customer1
    GO

    BEGIN TRAN Add_Customer2
    INSERT INTO Customer_extra
    (CustomerID, CompanyName, Address, City, State, PostalCode, Country)
    VALUES
    (@CustomerID, @CompanyName, @Address, @City, @State, @PostalCode, @Country)
    EXEC add_customer
    COMMIT TRAN Add_Customer2
    GO

    I get an error that I must declare the variable "CustomerID". If I omit The "GO" in the "BEGIN TRAN Add_Customer1", everything seems OK in SQL. But still I receive an error on the webpage that request the procedure. Someone that can help me with this?
    Last edited by alshadar; 11-06-03 at 10:50.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    remove the GO inbetween the two inserts, GO's will cancel out your variables that you have set.

  3. #3
    Join Date
    Nov 2003
    Location
    Norway
    Posts
    6

    Re: ....

    The error was that I used a GO and an EXEC that I shouldn't use. I read a book and they were using Query Analyzer for their code. I wrote it in a stored procedure. That's my mistake. But now, everything works ok. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •