SQL 创建标量函数 获取 类别 深度

/****** Object:  UserDefinedFunction [dbo].[Func_GoodsclassDeep]    Script Date: 02/05/2015 08:57:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2015-1-30
-- Description:	获取指定类别id记录的深度
-- =============================================
ALTER FUNCTION [dbo].[Func_Goods_Class_Deep] ( @id INT )
RETURNS INT
AS 
    BEGIN
        DECLARE @deep INT
        DECLARE @tempid INT 
        SET @deep = 0
        SET @tempid = 0
        WHILE @id != 0 
            BEGIN
                SELECT  @tempid = parentid
                FROM    tb_goods_class
                WHERE   id = @id 
                IF  @tempid >0
                    AND @tempid != @id 
                    BEGIN
                        SET @id = @tempid
                        SET @deep = @deep + 1 
                    END 
                ELSE 
                    BEGIN 
                        SET @id = 0
                    END
            END
        RETURN @deep

    END

<pre name="code" class="sql">调用方法 

select dbo. Func_Goods_Class_Deep(id) from goods_class
可以直接 当列来使用