您现在正在浏览：站长之家 >> 数据库 >> 详细内容

用排序串字段实现树状结构（数据库存储过程）

时间:2007-12-23 来源:不详作者:迈克DB

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--·￠D?ìù
goto newin
ELSE
begin
SELECT @lybid=lybid,@rootid=rootid,@ostr=orderstr,@recimail=guestemail,@eflag=emailflag from guestbook

本文来自织梦

where lybid=@keyid
IF @lybid=0--???′ìù???òμ?￡?μ±D?ìù·￠±í
goto newin
ELSE
BEGIN
if (@eflag=0 and @guestemail<>'swuse@21cn.com abc') select @recimail=''--è?1?ê?°??÷???′?ò???¨·￠óê?t??ìá
?ê??￡??ò2?1ü·￠ìù??ê?·?òa?ó???′￡?oó??μ?abc?àμ±óú1üàí?ü??
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)

é?ìù￡¨???|￡?′?′￠1y3ì￡o 织梦好，好织梦

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 　　 asp">存储过程