Store Procedure Output Parameter INSERTED.ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Store Procedure Output Parameter INSERTED.ID

Author

Topic

swissivan
Starting Member

30 Posts

Posted - 2013-03-03 : 20:48:43

I want to write a store procedure that return the inserted id to my c# program

@id uniqueidentifier OUTPUT

INSERT INTO product(name,...,etc)
OUTPUT INSERTED.id, INSERTED.mycolumn
VALUES ('myname',...,etcvalue)

I want to pass the OUTPUT INSERTED.id to @id
How can do this? thanks

INSERTED.id is in type uniqueidentifier

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 21:00:46

[code]
@id uniqueidentifier OUTPUT
DECLARE @idTbl TABLE (id UNIQUEIDENTIFIER);

INSERT INTO product(name,...,etc)
OUTPUT INSERTED.id INTO @idTbl
OUTPUT INSERTED.id, INSERTED.mycolumn
VALUES ('myname',...,etcvalue)

SELECT TOP (1) @id = id FROM @idTbl;[/code]Of course, if you have more to the values clause (i.e., you are inserting more than one row via the insert statement), you will get only one of the values in the @id variable.

swissivan
Starting Member

30 Posts

Posted - 2013-03-03 : 21:22:55

quote:
Originally posted by James K

@id uniqueidentifier OUTPUT
DECLARE @idTbl TABLE (id UNIQUEIDENTIFIER);

INSERT INTO product(name,...,etc)
OUTPUT INSERTED.id INTO @idTbl
OUTPUT INSERTED.id, INSERTED.mycolumn
VALUES ('myname',...,etcvalue)

SELECT TOP (1) @id = id FROM @idTbl;
Of course, if you have more to the values clause (i.e., you are inserting more than one row via the insert statement), you will get only one of the values in the @id variable.

It works great, thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-03 : 22:30:37

quote:
Originally posted by James K

@id uniqueidentifier OUTPUT
DECLARE @idTbl TABLE (id UNIQUEIDENTIFIER);

INSERT INTO product(name,...,etc)
OUTPUT INSERTED.id INTO @idTbl
OUTPUT INSERTED.id, INSERTED.mycolumn
VALUES ('myname',...,etcvalue)

SELECT TOP (1) @id = id FROM @idTbl;
Of course, if you have more to the values clause (i.e., you are inserting more than one row via the insert statement), you will get only one of the values in the @id variable.

just using top 1 wont guarantee the order of retrieval

you might need to use ORDER BY id DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources