笔记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