Stored Procedures | Sorting add-in stored procedures:
if exists (select * from sysobjects where id = object_id ("Lybsave"))
drop proc Lybsave
CREATE PROCEDURE [Lybsave] @keyid int=0, @guestname varchar, @guestitle varchar (MB), @guestcomm text, @guestemail varchar = ', @emailflag bit=0, @fromip varchar, @recimail varchar (m) OUTPUT
As
DECLARE @ostr varchar, @rootid int, @lybid int, @ostrs varchar (), @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--New post
Goto Newin
ELSE
Begin
SELECT @lybid =lybid, @rootid =rootid, @ostr =orderstr, @recimail =guestemail, @eflag =emailflag from guestbook where lybid= @keyid
If @lybid = 0--The reply post is not found, when the new post is posted
Goto Newin
ELSE
BEGIN
if (@eflag =0 and @guestemail <> ' swuse@21cn.com abc ') Select @recimail = '--if the moderator replies and assigns an email to the questioner, whether or not the sender requests a reply, The following ABC is the equivalent of admin password
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)
Delete paste (prune) stored procedure:
if exists (select * from sysobjects where id = object_id ("Lybdel"))
drop proc Lybdel
CREATE PROCEDURE [Lybdel] @keyid int
As
DECLARE @ostr varchar (), @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
Software Use homepage http://swuse.yeah.net authoring
