用排序串字段实现树状结构（存储过程） -pc6资讯

您的位置：首页 → 技术开发 → ASP技巧 → 用排序串字段实现树状结构（存储过程）

用排序串字段实现树状结构（存储过程） 时间：2004/11/7 4:02:00来源：本站整理作者：蓝点我要评论(0): 加贴存储过程：

if exists (select * from sysobjects where id = object_id("lybsave"))

   drop proc lybsave

CREATE PROCEDURE [lybsave] @keyid int=0,@guestname varchar(20),@guestitle varchar(100),@guestcomm

text,@guestemail varchar(50)='',@emailflag bit=0,@fromip varchar(15),@recimail varchar(50) OUTPUT

AS

DECLARE @ostr varchar(30),@rootid int,@lybid int,@ostrs varchar(30),@l tinyint,@tdt datetime,@putdate

varchar(10),@puttime varchar(5),@eflag bit

select @tdt=getdate()

select @putdate=convert(varchar(4),datepart(yy,@tdt))+'-'+left('0'+convert(varchar(2),datepart(mm,@tdt)),2)

+'-'+left('0'+convert(varchar(2),datepart(dd,@tdt)),2)

select @puttime=left('0'+convert(varchar(2),datepart(hh,@tdt)),2)+':'+left('0'+convert(varchar(2),datepart

(mi,@tdt)),2)

select @ostr='',@rootid=0,@lybid=0,@l=0

if (@guestemail='') select @emailflag=0

If @keyid=0  --发新贴

  goto newin

ELSE

begin

  SELECT @lybid=lybid,@rootid=rootid,@ostr=orderstr,@recimail=guestemail,@eflag=emailflag from guestbook

where lybid=@keyid

  IF @lybid=0  --回复贴没找到，当新贴发表

   goto newin

  ELSE

   BEGIN

    if (@eflag=0 and @guestemail<>'swuse@21cn.com abc') select @recimail=''  --如果是版主回复且指定发邮件给提

问者，则不管发贴者是否要求回复，后面的abc相当于管理密码

    if (@rootid=0) select @rootid=@lybid

    select @ostrs=@ostr+'%',@lybid=0

    select top 1 @lybid=lybid,@ostrs=orderstr from guestbook where rootid=@rootid and (orderstr like

@ostrs) and lybid<>@keyid order by orderstr

    if (@lybid=0) select @ostr=@ostr+char(122)

    else

     begin

      select @l=len(@ostrs)

      select @ostr=left(@ostrs,@l-1)+char(ascii(substring(@ostrs,@l,1))-1)

     end

    goto newin

   end

end

newin:

    INSERT into guestbook

(guestname,guestitle,guestcomm,putdate,puttime,guestemail,emailflag,rootid,fromip,orderstr) values

(@guestname,@guestitle,@guestcomm,@putdate,@puttime,rtrim(@guestemail),@emailflag,@rootid,@fromip,@ostr)

删贴（剪枝）存储过程：

if exists (select * from sysobjects where id = object_id("lybdel"))

   drop proc lybdel

CREATE PROCEDURE [lybdel] @keyid int

AS

DECLARE @ostr varchar(30),@rootid int,@lybid int

select @ostr='',@rootid=0,@lybid=0

SELECT @ostr=orderstr,@rootid=rootid,@lybid=lybid from guestbook where lybid=@keyid

if (@lybid<>0)

  BEGIN

    if (@rootid=0) select @rootid=@lybid

    SELECT @ostr=@ostr+'%'

    DELETE FROM guestbook where orderstr like @ostr and rootid=@rootid or lybid=@rootid

  END

文章评论

查看所有0条评论>>

热门文章 没有查询到任何记录。