如何在使用存储过程插入数据表时获取范围标识列

Question

我正在使用存储过程插入数据表，并在之前创建了一个类型表， 查询是我想取回此会话中插入的所有"ProdID"。 对于单个插入，我可以获取范围标识，但我想获取最近插入的所有标识。

提前谢谢。

[dbo].[sp_Isert] (@dt_Product Product_Table READONLY, @ProdID int out)  
AS
INSERT into tblProduct (Name,Batch,Qty,ExpDate)
SELECT Name, Batch, Qty, ExpDate
FROM  @dt_Product;
set @ProdID = Scope_Identity( )
select Scope_Identity( ) ProdID

Answer 1

不要使用scope_identity()- 改用output子句。 请注意，SQL Server 不支持表值参数作为输出参数，这意味着从存储过程返回记录集的唯一方法是使用output子句(而不是 in table(或执行select语句。

此外，不要对存储过程使用sp前缀。
Microsoft 对系统进程使用此前缀，因此您可能会遇到名称冲突。

ALTER PROCEDURE [dbo].[stp_Isert] (@dt_Product Product_Table READONLY)  
AS
INSERT into tblProduct (Name,Batch,Qty,ExpDate)
OUTPUT Inserted.Id -- This will return a recordset with the inserted ids to the calling application.
SELECT Name, Batch, Qty, ExpDate
FROM  @dt_Product;

更新

我制作了一个示例脚本供您检查。当我在 SQL Server 实例上运行它时，我得到了预期的结果：

CREATE TABLE tbl_TestOutputClause (Id int identity(1,1), Col int );
GO
CREATE TYPE udt_TestOutputClauseIntegers AS TABLE (Value int);
GO
CREATE PROCEDURE stp_TestOutputClauseInsert (@Values dbo.udt_TestOutputClauseIntegers READONLY)
AS
INSERT INTO tbl_TestOutputClause(Col)
OUTPUT INSERTED.Id
SELECT Value
FROM @Values;
GO
CREATE PROCEDURE stp_TestOutputClauseGetInsertedValues 
AS
DECLARE @Ids AS TABLE (Id int);
DECLARE @Vals dbo.udt_TestOutputClauseIntegers;
INSERT INTO @Vals (Value) VALUES (1), (2), (3);
INSERT INTO @Ids
EXEC stp_TestOutputClauseInsert @Vals;
-- should return three rows with the values 1, 2 and 3.    
SELECT *
FROM @Ids;
GO
EXEC stp_TestOutputClauseGetInsertedValues;
-- clean up
DROP TABLE tbl_TestOutputClause;
DROP PROCEDURE stp_TestOutputClauseInsert;
DROP PROCEDURE stp_TestOutputClauseGetInsertedValues 
DROP TYPE udt_TestOutputClauseIntegers;