需要一个插入行并返回ID的存储过程

I tried to write a stored procedure that first inserts a new record into table and then returned the id of this new record. I am not sure if it is the correct way and the best way to achieve this.

我尝试编写一个存储过程，首先将新记录插入表中，然后返回此新记录的id。我不确定这是否是正确的方法和实现这一目标的最佳方式。

ALTER PROCEDURE dbo.spAddAsset
(
@Name VARCHAR(500),
@URL VARCHAR(2000)
)
AS
BEGIN
Set NOCOUNT on;

Insert Into Assets (Name, URL) Values (@Name, @URL)

Declare @new_identity int;

SELECT @new_identity = SCOPE_IDENTITY()

return @new_identity;
END

1 个解决方案

#1

To return a single scalar value to the caller you should use an OUTPUT parameter, not RETURN. RETURN is for error/status codes. Also the prefix sp is redundant and unnecessary.

要将单个标量值返回给调用者，您应该使用OUTPUT参数，而不是RETURN。 RETURN用于错误/状态代码。前缀sp也是冗余且不必要的。

CREATE PROCEDURE dbo.AddAsset
  @Name VARCHAR(500),
  @URL  VARCHAR(2000),
  @new_identity INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Assets(Name, URL) SELECT @Name, @URL;
    SET @new_identity = SCOPE_IDENTITY();
END
GO

Then to call it:

然后叫它：

DECLARE @new_identity INT;
EXEC dbo.AddAsset @Name = 'a', @URL = 'b', @new_identity = @new_identity OUTPUT;
PRINT @new_identity;

EDIT just adding a disclaimer that won't affect the asker in this specific scenario, but may help in other scenarios or for future readers. In SQL Server 2008 R2 and earlier, there is a potentially nasty bug with built-in functions such as SCOPE_IDENTITY when parallelism is used to derive the results to be inserted (think INSERT FROM othertable). This bug (here is the Connect item) is fixed in Cumulative Update #5 for SQL Server 2008 R2 SP1, but so far a fix has not appeared for 2008 R2 RTM, 2008 or 2005.

编辑只是添加了一个不会影响这个特定场景中的提问者的免责声明，但可能有助于其他场景或未来的读者。在SQL Server 2008 R2及更早版本中，当使用并行性派生要插入的结果时，可能存在一个内置函数（例如SCOPE_IDENTITY）的令人讨厌的错误（想想INSERT FROM othertable）。此错误（此处为Connect项目）已在SQL Server 2008 R2 SP1的累积更新＃5中得到修复，但到目前为止还没有针对2008 R2 RTM，2008或2005进行修复。

#1