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.
Go to Top of Page

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!
Go to Top of Page

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/

Go to Top of Page
   

- Advertisement -