Returning OUTPUT value problem

[aimlion11yahoo]

I have a PROC name MAIN.
MAIN calls 2 other PROCs: SUB1 and SUB2
When SUB1 is called, it does a INSERT INTO on the table ORDERS.
A automatic number is generated for the OrderID field
When SUB2 is called, it does a INSERT INTO on the table ORDER_DETAILS and the insert must contain the OrderID generated in SUB1.

I am using SQL Query Analyzer to test this out, but I cannot return the @OrderID in order to pass it to SUB2 unless I remove BEGIN and END from SUB1.

Is there a rule that you can return OUTPUT values if you have a BEGIN and END in your procedure?

 

[mrdenny]

No, there are no such rules. Can you post some of your code.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[aimlion11yahoo]

I trimmed things down a bit so it is simplier to understand. In the code below, I am inserting only the customers last name, and trying to return the automatically generated OrderID.

' Code in SQL Query Analyzer
declare @OrderID as int
exec cbd_AddOrder 'Smith', @OrderID
print @OrderID

'This is the stored procedure
CREATE PROCEDURE dbo.cbd_AddOrder
(
@name nvarchar(50),
@OrderID int OUTPUT
)
 AS
BEGIN
	insert into Orders (Name) values (@name)
	SELECT @OrderID = @@IDENTITY'               
END
GO

 

[mrdenny]

You need to do this.

declare @OrderID as int
exec cbd_AddOrder 'Smith', @OrderID [red]OUTPUT[/red]
print @OrderID

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[aimlion11yahoo]

Duh! Thanks, Denny.

 

[mrdenny]

You should also look into using the scope_identity function instead of @@IDENTITY. If you ever put a trigger on the Orders table and that trigger does an insert into another table with an identity column, the @@IDENTITY will return the incorrect value. scope_identity doesn't have this problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com