叮当!削习吧~~

导航

存储过程和函数的一些范例

存储过程

是将栏目从上至下由>拼接,跟导航条类似。例如:首页>信息公开>机构职能

USE [MiddleHospital] GO

/****** Object:  StoredProcedure [dbo].[GetSubjectPath]    Script Date: 08/26/2013 15:24:49 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

 

CREATE procedure [dbo].[GetSubjectPath]

@code varchar(100),@rtn varchar(1000) output

as

set @rtn=''

begin

  declare @tempName varchar(100)

  declare @rows int

  set @rows=1

    declare cursor1 cursor for select name from dbo.GetParentTreeInfoByCode(@code) where issubwebsite=0 order by levels   --声明游标

    open cursor1     --打开游标

  fetch next from cursor1 into @tempName  --@tempName应该就是表中name的值

  while @@fetch_status=0   --让游标指向第一个值

    begin    

      if(@@cursor_rows=@rows)     --当游标的行数等于表的行数(也就是游标指向最后一行)

        begin      

          set @rtn=@rtn+@tempName;     

        end    

      else     

        begin      

          set @rtn=@rtn+@tempName+'>';     

        end    

      fetch next from cursor1 into @tempName  --游标指向下一个

          set @rows=@rows+1   --行数加1

    end

  close cursor1

  deallocate cursor1

end

select @rtn as subjectPath

GO

 

同样的功能标量值函数又是怎么做的呢?其实大相近庭

USE [MiddleHospital] GO

/****** Object:  UserDefinedFunction [dbo].[GetSubectPathByCode]    Script Date: 08/26/2013 15:32:49 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

 

CREATE FUNCTION [dbo].[GetSubectPathByCode]

(  

  @code varchar(100)

)

RETURNS varchar(1000)

AS

begin

  declare @rtn varchar(1000)  

  set @rtn=''

  declare @tempName varchar(100)

  declare @rows int

  set @rows=1  

  declare cursor1 cursor for select name from dbo.GetParentTreeInfoByCode(@code) where issubwebsite=0 order by levels    

  open cursor1    

  fetch next from cursor1 into @tempName  

  while @@fetch_status=0   

    begin    

      if(@@cursor_rows=@rows)     

        begin      

          set @rtn=@rtn+@tempName;     

        end    

      else     

        begin      

          set @rtn=@rtn+@tempName+'>';     

        end    

      fetch next from cursor1 into @tempName    

      set @rows=@rows+1   

    end

  close cursor1

  deallocate cursor1

  return @rtn

end

GO

 

 

posted on 2013-08-26 15:37  叮当!削习吧~~  阅读(143)  评论(0编辑  收藏  举报