常用sql自定義函數以及存儲過程

------------------------------------

--用途：傳回給定類别id的樹路徑[取其名稱]

--說明：父節點與子節點路徑以>>聯結,改為自定義後使用該函數

--時間：2011-1-13 16:40:28

------------------------------------

CREATE      FUNCTION Getpath

               (@id INT)

RETURNS VARCHAR(1000)

AS

  BEGIN

    DECLARE

      @re_str  AS VARCHAR(1000)

    SET @re_str = ''

    SELECT @re_str = classname

    FROM   uds_class

    WHERE  classid = @id

    WHILE EXISTS (SELECT 1

                  FROM   uds_class

                  WHERE  classid = @id

                         AND classparentid <> classid)

      BEGIN

        SELECT @id = b.classid,

               @re_str = b.classname + '>>' + @re_str

        FROM   uds_class a,

               uds_class b

        WHERE  a.classid = @id

               AND a.classparentid = b.classid

      END

    RETURN @re_str

  END

------------------------------------

--用途：傳回目前文檔多個關鍵詞構造的參照表@tb

--說明：原始關鍵詞以空格分割,改為自定義後使用該函數

--時間：2011-1-13 16:40:28

------------------------------------

CREATE FUNCTION GetRelaTag(@docid int)  

RETURNS @tb table([name] varchar(20)) AS

begin

declare @index int

declare @name varchar(20),@tagstr varchar(500)

select @tagstr=tagnamestr from tss_document where [email protected]

if @tagstr<>'' set @[email protected]+space(1)

while(@tagstr<>'')

begin

set @index = charindex(space(1),@tagstr)

set @name = substring(@tagstr,1,@index-1)

set @tagstr = substring(@tagstr,@index+1,len(@tagstr)[email protected])

insert into @tb values(@name)

end

return

end

------------------------------------

--用途：獲得與目前資料相關的資源

--說明：關鍵詞改為自定義後使用該過程,用到自定義函數GetRelaTag

--時間：2011-1-13 16:40:28

------------------------------------

CREATE  PROCEDURE usp_getrelakey3(

               @docid INT,

               @topN  INT)

AS

SELECT IDENTITY(INT,1,1) as tid,* INTO #tmp FROM GetRelaTag(@docid)

DECLARE @relatable TABLE (

    docid INT,

    tagnamestr  varchar(500))

  DECLARE @resulttable TABLE (

    docid INT,

    score INT) --依據關鍵詞比對度加分

  INSERT INTO @relatable  SELECT   docid,tagnamestr  FROM  tss_document          

   WHERE   docid<>@docid and status='釋出' and tagnamestr is not null  ORDER BY docid

  DECLARE  @min INT

  SELECT @min = Min(docid)  FROM   @relatable

  WHILE @min IS NOT NULL

    BEGIN

      BEGIN        

        DECLARE @mininner INT

        SELECT @mininner = Min(tid)  FROM   #tmp

        WHILE @mininner IS NOT NULL

          BEGIN

            IF EXISTS (SELECT *  FROM   #tmp  WHERE [email protected] and  [name] IN (SELECT *  FROM   GetRelaTag(@min)))

              BEGIN

                IF EXISTS (SELECT *  FROM  @resulttable  WHERE  docid = @min) --已存在則更新分數

                  UPDATE @resulttable  SET score = Isnull(score,0) + 1  WHERE  docid = @min

                 ELSE

                  INSERT INTO @resulttable   VALUES(@min,1)

              END

            SELECT @mininner = Min(tid)  FROM   #tmp  WHERE  tid> @mininner

          END

      END

      SELECT @min = Min(docid)   FROM   @relatable   WHERE  docid > @min --更新“指針”内容，使之移到下一記錄

    END

  SET ROWCOUNT  @topN

  SELECT   b.*

  FROM     @resulttable a

           LEFT JOIN tss_document b

             ON a.docid = b.docid

  WHERE b.status='釋出'

  ORDER BY a.score DESC

GO

------------------------------------

--用途： 獲得給定類别id的所有父類包括其本身

--說明：注意自聯結的使用

--時間：2011-1-13 16:40:28

------------------------------------

CREATE  FUNCTION getallparentInfor

              (@id INT)

RETURNS @t TABLE(classid INT,className varchar(300))

AS

  BEGIN

    declare @CName as varchar(300)

    select top 1 @CName=className from uds_class where [email protected]

    INSERT INTO @t

    VALUES     (@id,@CName)

    WHILE EXISTS (SELECT 1

                  FROM   uds_class

                  WHERE  classid = @id

                         AND classparentid <> classid)

      BEGIN

        SELECT @id = b.classid,@CName=b.className

        FROM   uds_class a,

               uds_class b

        WHERE  a.classid = @id

               AND a.classparentid = b.classid

        INSERT INTO @t

        VALUES     (@id,@CName)

      END

    RETURN

  END

------------------------------------

--用途： 獲得給定類别id的所有子類但不包括其本身

--時間：2011-1-13 16:40:28

------------------------------------

create function GetChildren (@id int)

returns @t table(classid int)

as

begin

    insert @t select classid from uds_class where classparentid = @id

    while @@rowcount > 0

        insert @t select a.ClassID from uds_class as a inner join @t as b

        on a.classparentid = b.classid and a.ClassID not in(select classid from @t)

   return

end

------------------------------------

--用途： 獲得給定類别id的所有子類包括其本身

--說明：用到GetChildren 函數

--時間：2011-1-13 16:40:28

------------------------------------

CREATE  function GetChildrenIncSelf(@ClassID int)

Returns table

AS

Return (select * from GetChildren(@ClassID)

union

select @ClassID ) --包含本身的類别id表