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?
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