笔记229 @@IDENTITY 全局变量解释2013-3-20

笔记229 @@IDENTITY 全局变量解释2013-3-20

--@@IDENTITY 全局变量解释2013-3-20
--返回最后插入的标识值的系统函数。 http://msdn.microsoft.com/zh-cn/library/ms187342.aspx
--以下示例向包含标识列(LocationID) 的表中插入一行，并使用 @@IDENTITY 显示新行中使用的标识值。

USE [AdventureWorks];
GO
--Display the value of LocationID in the last row in the table.
SELECT MAX (LocationID) FROM Production .Location;
GO
INSERT INTO Production.Location (Name, CostRate, Availability , ModifiedDate)
VALUES ( 'Damaged Goods', 5 , 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';
GO
--Display the value of LocationID of the newly inserted row.
SELECT MAX (LocationID) FROM Production .Location;
GO

---------------------老张找出公司代码-------------------------------------
ALTER     FUNCTION [dbo].[Company_GetCompanyCodes] (@CompanyCode varchar(40))
RETURNS @temp TABLE (CompanyCode varchar(40))
AS
begin
       DECLARE @tmpCompanyCode VARCHAR (40)
    -- 用法select * from Company_GetCompanyCodes('22')
       --INSERT INTO @temp VALUES(@CompanyCode)
       IF(EXISTS (SELECT CompanyID FROM Company WHERE ParentCompanyCode=@CompanyCode))
       BEGIN
             INSERT INTO @temp VALUES (@CompanyCode)
             DECLARE @i INT
             DECLARE @total INT
             DECLARE @tmp1 TABLE (theid int IDENTITY(1,1) NOT NULL, CompanyCode VARCHAR(40))
             INSERT INTO @tmp1(CompanyCode)
             SELECT CompanyCode FROM Company WHERE ParentCompanyCode=@CompanyCode
             set @total=@@IDENTITY
             SET @i=1
             WHILE(@i<=@total)
             BEGIN
                   SELECT @tmpCompanyCode= CompanyCode FROM @tmp1 WHERE theid=@i
                   IF(EXISTS (SELECT CompanyID FROM Company WHERE ParentCompanyCode=@tmpCompanyCode))
                   BEGIN
                         INSERT INTO @temp(CompanyCode)
                         SELECT CompanyCode FROM Company_GetCompanyCodes(@tmpCompanyCode)
                   END
                   ELSE
                   BEGIN
                         INSERT INTO @temp VALUES (@tmpCompanyCode)
                   END
                  
                   SET @i=@i+1
             END
       END
       ELSE
       BEGIN
             INSERT INTO @temp VALUES (@CompanyCode)
       END
   RETURN
end