笔记229 @@IDENTITY 全局变量解释2013-3-20
1 --@@IDENTITY 全局变量解释2013-3-20
2 --返回最后插入的标识值的系统函数。 http://msdn.microsoft.com/zh-cn/library/ms187342.aspx
3 --以下示例向包含标识列(LocationID) 的表中插入一行，并使用 @@IDENTITY 显示新行中使用的标识值。
4
5 USE [AdventureWorks];
6 GO
7 --Display the value of LocationID in the last row in the table.
8 SELECT MAX (LocationID) FROM Production .Location;
9 GO
10 INSERT INTO Production.Location (Name, CostRate, Availability , ModifiedDate)
11 VALUES ( 'Damaged Goods', 5 , 2.5, GETDATE());
12 GO
13 SELECT @@IDENTITY AS 'Identity';
14 GO
15 --Display the value of LocationID of the newly inserted row.
16 SELECT MAX (LocationID) FROM Production .Location;
17 GO
18
19 ---------------------老张找出公司代码-------------------------------------
20 ALTER FUNCTION [dbo].[Company_GetCompanyCodes] (@CompanyCode varchar(40))
21 RETURNS @temp TABLE (CompanyCode varchar(40))
22 AS
23 begin
24 DECLARE @tmpCompanyCode VARCHAR (40)
25 -- 用法select * from Company_GetCompanyCodes('22')
26 --INSERT INTO @temp VALUES(@CompanyCode)
27 IF(EXISTS (SELECT CompanyID FROM Company WHERE ParentCompanyCode=@CompanyCode))
28 BEGIN
29 INSERT INTO @temp VALUES (@CompanyCode)
30 DECLARE @i INT
31 DECLARE @total INT
32 DECLARE @tmp1 TABLE (theid int IDENTITY(1,1) NOT NULL, CompanyCode VARCHAR(40))
33 INSERT INTO @tmp1(CompanyCode)
34 SELECT CompanyCode FROM Company WHERE ParentCompanyCode=@CompanyCode
35 set @total=@@IDENTITY
36 SET @i=1
37 WHILE(@i<=@total)
38 BEGIN
39 SELECT @tmpCompanyCode= CompanyCode FROM @tmp1 WHERE theid=@i
40 IF(EXISTS (SELECT CompanyID FROM Company WHERE ParentCompanyCode=@tmpCompanyCode))
41 BEGIN
42 INSERT INTO @temp(CompanyCode)
43 SELECT CompanyCode FROM Company_GetCompanyCodes(@tmpCompanyCode)
44 END
45 ELSE
46 BEGIN
47 INSERT INTO @temp VALUES (@tmpCompanyCode)
48 END
49
50 SET @i=@i+1
51 END
52 END
53 ELSE
54 BEGIN
55 INSERT INTO @temp VALUES (@CompanyCode)
56 END
57 RETURN
58 end
