经常会遇见要在存储过程中获得刚刚插入成功记录的自增长列的数据，SQL server给我们提供了一个全局变量@@identity,它可以获得刚刚插入成功的自增列列的信息，需要注意，如果一次插入多条数据，这个@@identity就是最后一条记录的自增列的信息；如果表中没有自增长列，@@identity的值就是NULL。

举例如下：

1 CREATE TABLE Orders(  2 OrderID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  3 OrderDate datetime ,  4 ShippeDate datetime,  5 ShipName  nvarchar(40),  6 ShipAddress    NVARCHAR(50),  7 ShipCity nvarchar(15),  8 ShipRegion    nvarchar(15),  9 ShipPostalCode    NVARCHAR(10) 10 )

 

1 CREATE TABLE [Order Details]  2 (  3 OrderID    INT ,  4 ProductID    INT,  5 UnitPrice Decimal(18,4),  6 Quantity    SMALLINT,  7 Discount    REAL  8 Constraint pk_OrderID_ProductID    primary key(OrderID,ProductID),  9 CONSTRAINT FK_OrderID_Details Foreign KEY (OrderID)    REFERENCES Orders(OrderID) 10 )

 

1 CREATE PROC usp_TestInsertIndentity  2 @ShippName    Nvarchar(40),  3 @ShipAddress NVARCHAR(50),  4 @ShipCity NVARCHAR(15),  5 @ShipRegion nvarchar(15),  6 @ShipPostalCode    NVARCHAR(10),  7 @ProductID    INT,  8 @UnitPrice DECIMAL(18,4),  9 @Quantity    SMALLINT, 10 @Discount   REAL, 11 @State  SMALLINT OUTPUT 12 AS 13     BEGIN 14     SET NOCOUNT ON; 15     DECLARE @rowsAffect SMALLINT =0; 16     BEGIN TRAN; 17         SET @State=-1; 18         INSERT INTO Orders(OrderDate,ShippeDate,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode) 19         VALUES(GETDATE(),GETDATE(),@ShippName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode); 20             SET    @rowsAffect=@@IDENTITY; 21             IF(@@ERROR<>0 OR @rowsAffect=0)  GOTO ERROR_Handler; 22         SET @State=-2; 23         INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount) VALUES(@rowsAffect,@ProductID,@UnitPrice,@Quantity,@Discount); 24             IF(@@ERROR<>0) GOTO ERROR_Handler; 25     COMMIT TRAN; 26     SET @State=0; 27     RETURN @State; 28 ERROR_Handler: 29     ROLLBACK TRAN; 30     RETURN @State; 31 END 32 GO

可以看到在上面存储过程中，把@@identity的值放到一个变量中(局部变量),这是为了规范,实质上第二条插入语句也可以这样写：

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount) VALUES(@@identity,@ProductID,@UnitPrice,@Quantity,@Discount);

如果后面还有插入语句，且此插入语句要用到到第一条插入语句插入成功后自增长列的值，刚刚的这种用法就不正确，因为@@IDENTITY获得最近一条插入成功的自增列的值，如果表中没有自增长列，@@identity就等于NULL；

调用存储过程的语句：

1 DECLARE @ShippName Nvarchar(40);  2 DECLARE @ShipAddress NVARCHAR(50);  3 DECLARE @ShipCity    NVARCHAR(15);  4 DECLARE @ShipRegion    NVARCHAR(15);  5 DECLARE @ShipPostalCode NVARCHAR(10);  6 DECLARE @ProductID    INT;  7 DECLARE @UnitPrice DECIMAL(18,4);  8 DECLARE @Quantity SMALLINT;  9 DECLARE @Discount REAL; 10 DECLARE @State SMALLINT; 11 SET @ShippName ='ebay'; 12 SET @ShipAddress='上海市虹口区'; 13 SET @ShipCity='中山北一路'; 14 SET @ShipRegion = '021'; 15 SET @ShipPostalCode='200080'; 16 SET @ProductID = 1000; 17 SET @UnitPrice=19.800; 18 SET @Quantity=10; 19 SET @Discount=0.97; 20 EXEC usp_TestInsertIndentity @ShippName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ProductID,@UnitPrice,@Quantity, @Discount, @State OUTPUT 21 print @State;

由于存储过程传入的参数很多，现在改版用表值变量。

分别创建2个表值变量：

1  CREATE TYPE Ty_Orders AS TABLE(  2  ShipName  nvarchar(40),  3  ShipAddress    NVARCHAR(50),  4  ShipCity nvarchar(15),  5  ShipRegion    nvarchar(15),  6  ShipPostalCode    NVARCHAR(10)  7  )  8  CREATE TYPE Ty_Orders_Details AS TABLE(  9 ProductID    INT, 10 UnitPrice Decimal(18,4), 11 Quantity    SMALLINT, 12 Discount    REAL 13  )

 

1 CREATE PROC usp_TypeTestInsertIndentity  2  @dtOrders Ty_Orders READONLY,  3  @dtOrdersDetails Ty_Orders_Details READONLY,  4  @State  SMALLINT OUTPUT  5 AS  6     BEGIN  7     SET NOCOUNT ON;  8     DECLARE @rowsAffect SMALLINT =0;  9     BEGIN TRAN; 10         SET @State=-1; 11         INSERT INTO Orders(OrderDate,ShippeDate,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode) 12         SELECT GETDATE(),GETDATE(), tt.ShipName,tt.ShipAddress,tt.ShipCity,tt.ShipRegion,tt.ShipPostalCode FROM @dtOrders tt; 13             SET    @rowsAffect=@@IDENTITY; 14             IF(@@ERROR<>0 OR @rowsAffect=0)  GOTO ERROR_Handler; 15         SET @State=-2; 16         INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount) SELECT @rowsAffect,ProductID,UnitPrice,Quantity,Discount FROM @dtOrdersDetails; 17             IF(@@ERROR<>0) GOTO ERROR_Handler; 18     COMMIT TRAN; 19     SET @State=0; 20     RETURN @State; 21 ERROR_Handler: 22     ROLLBACK TRAN; 23     RETURN @State; 24 END 25 GO

1  DECLARE @dtOrders Ty_Orders; 2  DECLARE @dtOrdersDetails Ty_Orders_Details; 3  DECLARE @state smallint; 4  INSERT INTO @dtOrders VALUES('IPAD2','松江','上海','CN','200080'); 5  INSERT INTO @dtOrdersDetails VALUES(1001,3280,10,0); 6  EXEC usp_TypeTestInsertIndentity @dtOrders,@dtOrdersDetails,@state output; 7  print @state;