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

Question

我尝试编写一个存储过程，首先将一条新记录插入表中，然后返回该新记录的 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

Answer 1

要向调用者返回单个标量值，您应该使用 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

然后调用它:

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

编辑只是添加免责声明，不会影响此特定场景中的提问者，但可能对其他场景或 future 的读者有所帮助。在 SQL Server 2008 R2 及更早版本中，有一个 potentially nasty bug当使用并行性导出要插入的结果时，可以使用 SCOPE_IDENTITY 等内置函数(想想 INSERT FROM othertable)。此错误( here is the Connect item )已在 Cumulative Update #5 for SQL Server 2008 R2 SP1 中修复。 ，但到目前为止，还没有出现针对 2008 R2 RTM、2008 或 2005 的修复程序。