Return inserted row from stored procedure

Question

I have the following stored procedure

CREATE PROCEDURE [dbo].[spInsert]
    @name nvarchar(128),
AS
    insert into NameIdentifier 
    ( Name, Identifier)
    values
    ( @name, NEWID());

SELECT @new_identity = SCOPE_IDENTITY()
SELECT * FROM NameAge where Id = @new_identity

Is there are a more efficient way to return the last inserted record complete with id and associated data?

That's not a valid sp, what is `@new_identity` ? you are not returning the last inserted row as you select from a different table? To return the new row in `NameIdentifier` you can use an `OUTPUT` clause. — Alex K., Nov 16 '16 at 16:19

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

Use Insertedwithin output clause,

As the follwoing:

CREATE PROCEDURE [dbo].[spInsert]
    @name nvarchar(128),
AS
DECLARE @MyTableVar table( 
                       Name varchar(50),
                       Identifier uniqueidentifier 
                       ;
    insert into NameIdentifier 
    ( Name, Identifier)
    values
    ( @name, NEWID());
     OUTPUT INSERTED.Name, INSERTED.Identifier
    INTO @MyTableVar
SELECt Name,Identifier from @MyTableVar

Refreance: Best way to get identity of inserted row?

edited May 23 '17 at 12:01

Community

1
1

answered Nov 16 '16 at 16:24

ahmed abdelqader

3,409
17
36

2

This is my preference too as using OUTPUT clause is also able to handle multiple records and it reduces potential issues with using @@IDENTITY etc. – Matt Nov 16 '16 at 16:27
In Transact-SQL, it's "insert into t1 output inserted.mycol values('x')", not "insert into...values...output" – Chris Apr 13 '21 at 15:15

score 1 · Answer 2 · answered Nov 16 '16 at 16:21

SCOPE_IDENTITY() is used to get the last generated Identity value in an identity column in your scope , For GUID values either you get the guid before you insert it like I have done in the code below , or you use OUTPUT clause to get the guid generated by the Insert statement.

CREATE PROCEDURE [dbo].[spInsert]
    @name nvarchar(128)
AS
BEGIN
  SET NOCOUNT ON;
  Declare @NewID UNIQUEIDENTIFIER =  NEWID();

    insert into NameIdentifier ( Name, Identifier)
    values( @name, @NewID);

SELECT * FROM NameAge where Id = @NewID;

END

score -2 · Answer 3 · answered Nov 16 '16 at 16:20

-2

This should give you your desired functionality.

CREATE PROCEDURE [dbo].[spInsert]
    @name nvarchar(128),
    @Ret int Output
AS
BEGIN
    insert into NameIdentifier 
    ( Name, Identifier)
    values
    ( @name, NEWID());

END
SET @Ret = @@IDENTITY

EDIT: This will return the id of your newly inserted row.

answered Nov 16 '16 at 16:20

Captain Squirrel

237
5
15

unless there is a trigger on the table that modifies any other record in anyway then the identity you get will NOT be the identity of the row inserted. https://blogs.msdn.microsoft.com/spike/2009/10/20/my-take-on-identity-vs-scope_identity/ – Matt Nov 16 '16 at 16:24
I've used that exact way of returning values in my current project and it works fine. (Without a trigger) – Captain Squirrel Nov 16 '16 at 16:26
1

In most basic configurations it should, but as SPs become more complex or multiple rows are involved, multiple tables etc @@IDENTITY is one of the easiest to introduce confusion and error, SCOPE_IDENTITY() is typically preferred for this but OUTPUT is more foolproof – Matt Nov 16 '16 at 16:29

score -2 · Answer 4 · answered Nov 16 '16 at 16:29

-2

You can achieve this by using the output clause of SQL Server, you can read more about this here

if exists (select 1 from sys.tables where name = 'NameIdentifier ')
    drop table NameIdentifier 
create table NameIdentifier 
(
    id BIGINT IDENTITY(1,1)
    ,Name VARCHAR(100)
    ,Identifier uniqueidentifier 
)

DECLARE @id TABLE 
(
    ID BIGINT
)

INSERT INTO NameIdentifier (Name,Identifier)
OUTPUT INSERTED.id INTO @id
VALUES('abcd',NEWID())

SELECT * FROM NameIdentifier N, @id I where N.id = I.id

answered Nov 16 '16 at 16:29

Surendra

711
5
15

@Matt if you observe the NEWID() is going into the column called as identifier, not the id column, the id column is still BIGINT. The above is a snippet that we can run any number of times without an issue or else the create statement will given you an error. – Surendra Nov 16 '16 at 16:36
Yeah you are right, I read too fast, you introduced a new BIGINT column which is not in OP. which I suppose is why you are trying to show a new table structure. Dropping table also isn't in OP and if someone is new to SQL tries your code without first removing DROP statement that could have disastrous consequences. This was the main reason for -1. And technique had already been answered by another individual when you posted. – Matt Nov 16 '16 at 16:43

Return inserted row from stored procedure

4 Answers4