从存储过程输出最后插入的主键值

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

我对这个问题有点困难,因为我不确定如何在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:

基本上,我想在数据库中插入一个新行,获取刚刚插入的PK值(相同的查询)并将其输出回任何称为存储过程的行:

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:

我在MySQL中完成了以下操作:

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:

SQL Server给我一个错误:

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

消息102,级别15,状态1,过程Users_Insert,第18行'@ userid'附近的语法不正确。

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

坦率地说,我不确定我是否正确地声明了输出参数,有人能提出建议吗?

1 个解决方案

#1

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

您需要将值分配给@userid!另外,我建议使用SCOPE_IDENTITY()而不是@@ 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

有关为什么要使用SCOPE_IDENTITY超过@@ IDENTITY的说明,请参阅此博客文章

#1