经常会遇见要在存储过程中获得刚刚插入成功记录的自增长列的数据，SQL server给我们提供了一个全局变量@@identity,它可以获得刚刚插入成功的自增列列的信息，需要注意，如果一次插入多条数据，这个@@identity就是最后一条记录的自增列的信息；如果表中没有自增长列，@@identity的值就是NULL。
举例如下：
Orders 主表
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
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
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 )
存储过程2
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
调用存储过程2
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;