Output last inserted primary key value from stored procedure

Question

I'm having a bit of difficulty with this one in that I'm not sure how to do this in SQL Server.

Basically, I want to insert a new row into the database, get the PK value that was just inserted (same query) and output it back to whatever called the stored procedure:

CREATE PROCEDURE Users_Insert
    -- Add the parameters for the stored procedure here
    @userid int output,
    @name varchar(50),
    @surname varchar(50),
    @email varchar(200),
    @password varchar(50),
    @location varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    insert into Users(FirstName, LastName, Email, Password, Location)
    values(@name, @surname, @email, @password, @location);
    GO
    @userid = @@IDENTITY;
END

I've done this in MySQL as follows:

CREATE PROCEDURE Users_Insert(@userid int output, @name varchar(50), @surname varchar(50), @email varchar(200), @password varchar(50), @location varchar(50)
BEGIN

    insert into Users(FirstName, LastName, Email, Password, Location)
    values(@name, @surname, @email, @password, @location);

    set @userid = last_insert_id();
END

SQL Server gives me an error:

Msg 102, Level 15, State 1, Procedure Users_Insert, Line 18
Incorrect syntax near '@userid'.

Frankly, I'm not sure I declared the output parameter correctly, can anyone offer suggestions?

Select SELECT @userid= SCOPE_IDENTITY(); and remove go its not necessary. — 4b0, May 10 '13 at 12:12

score 14 · Accepted Answer · answered May 10 '13 at 12:11

14

You need to assign the value to @userid ! Also, I would recommend using SCOPE_IDENTITY() and not @@IDENTITY :

CREATE PROCEDURE Users_Insert
    -- Add the parameters for the stored procedure here
    @userid int output,
    @name varchar(50),
    @surname varchar(50),
    @email varchar(200),
    @password varchar(50),
    @location varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    insert into Users(FirstName, LastName, Email, Password, Location)
    values(@name, @surname, @email, @password, @location);

    -- make an actual **assignment** here...
    SELECT @userid = SCOPE_IDENTITY();
END

See this blog post for an explanation as to WHY you should use SCOPE_IDENTITY over @@IDENTITY

answered May 10 '13 at 12:11

marc_s

732,580
175
1,330
1,459

1

@Ortund: you had **no assigment** - you just had `@userid = ...` - not `SET` or `SELECT` keyword – marc_s May 10 '13 at 12:13
`Must declare the scalar varialbe "@userid"` ... whenever I get onto sql server, it's all just problems, problems, problems... this is why I moved to mysql >_> – Ortund May 10 '13 at 12:14
1

The `GO` in the middle of the procedure is not valid. @Ortund - If you don't type valid syntax yes that will cause you problems. Sure that is the same in MySQL too. – Martin Smith May 10 '13 at 12:14
Now I'm getting `An SqlParameter with ParameterName '' is not contained by this SqlParameterCollection.` ... I assume this means that the SP is expecting a parameter called '' ? – Ortund May 10 '13 at 12:20
@Ortund: that's a problem in your **calling** code (C# or VB.NET) - that's a totally different question - ask it as a separate question. – marc_s May 10 '13 at 12:21
issue with scope identity...........http://connect.microsoft.com/SQLServer/feedback/details/328811/ – Amit Singh May 10 '13 at 12:22

Output last inserted primary key value from stored procedure

1 Answers1