April 25, 2018 at 12:05 am
CREATE PROC [dbo].[spInsertOrder]
@OrderID INT OUTPUT
AS
INSERT INTO dbo.Orders
...
SELECT @OrderID = @@IDENTITY
With above snippet, sql server will find last order id, saved it into @@IDENTITY, after assigns that value to @OrderID
DECLARE @MyIdent INT
EXEC [dbo].[spInsertOrder]
...
@OrderID = @MyIdent OUTPUT
SELECT * FROM dbo.Orders
WHERE OrderID = @MyIdent
For this snippet, I should understand that @OrderID was storing value from sproc, after push it to @MyIdent or @MyIdent was storing value of @OrderID (sproc), when execute it'll assign value to @OrderID?
Thanks for your reading, any ideas ...
April 25, 2018 at 12:23 am
nhatnghe39 - Wednesday, April 25, 2018 12:05 AM
CREATE PROC [dbo].[spInsertOrder]
@OrderID INT OUTPUT
AS
INSERT INTO dbo.Orders
...
SELECT @OrderID = @@IDENTITYWith above snippet, sql server will find last order id, saved it into @@IDENTITY, after assigns that value to @OrderID
DECLARE @MyIdent INT
EXEC [dbo].[spInsertOrder]
...
@OrderID = @MyIdent OUTPUTSELECT * FROM dbo.Orders
WHERE OrderID = @MyIdentFor this snippet, I should understand that @OrderID was storing value from sproc, after push it to @MyIdent or @MyIdent was storing value of @OrderID (sproc), when execute it'll assign value to @OrderID?
Thanks for your reading, any ideas ...
Your understanding is correct in this case.
HOWEVER, be vary careful with @@IDENTITY. It is not guaranteed to give you YOUR latest inserted identity value. It could give you the latest inserted identity value from any session. Rather use SCOPE_IDENTITY
April 25, 2018 at 12:41 am
Well, I only wonder why we can assign @MyIdent to @OrderID right away after declaring it, maybe I will assume that @OrderID passed out from sproc to execute, when run this line:
@OrderID = @MyIdent OUTPUT
SQL Server will assign value from @OrderID to @MyIdent...
April 25, 2018 at 1:36 am
nhatnghe39 - Wednesday, April 25, 2018 12:41 AMWell, I only wonder why we can assign @MyIdent to @OrderID right away after declaring it, maybe I will assume that @OrderID passed out from sproc to execute, when run this line:
@OrderID = @MyIdent OUTPUTSQL Server will assign value from @OrderID to @MyIdent...
Before calling proc
1 - Value of @MyIdent is assigned to @OrderID, and is available for use in the proc. As it is an OUTPUT param, it can be changed by the proc.
In Proc ...
1 - INSERT into table
2 - Table IDENTITY increases
3 - @@IDENTITY is assigned latest IDENTITY value
4 - @@IDENTITY value assigned to @OrderID, and passed out
After calling proc
1 - Value of @OrderID is assigned to @MyIdent
Below is a temp proc that shows how the value can be used, and changed.CREATE PROCEDURE #TestOutParam
@ParamVal INT OUTPUT
AS
BEGIN
SELECT InputParamVal = @ParamVal;
SET @ParamVal += 10;
SELECT OutputParamVal = @ParamVal;
END;
GO
DECLARE @MyVal INT = 5;
EXEC #TestOutParam @ParamVal = @MyVal OUTPUT;
SELECT MyVal = @MyVal;
GO
DROP PROCEDURE dbo.#TestOutParam;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply