Print the last inserted primary key value from the stored procedure

Question

I have a little difficulty with this in that I am not sure how to do this in SQL Server.

Basically, I want to insert a new row into the database, get the just inserted PK value (the same query) and return it back to everything called a 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 did 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, Users_Insert Procedure, Line 18
Invalid syntax near '@userid'.

Honestly, I'm not sure I correctly declared the output parameter, can anyone suggest suggestions?

Answer 1

You need to assign @userid value! Additionally, I would recommend using SCOPE_IDENTITY() rather than @@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 of WHY you should use SCOPE_IDENTITY over @@IDENTITY