Not executing ELSE statement? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Not executing ELSE statement?

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-09-06 : 11:09:34

Hi,

I'm wondering if anyone can offer any help with the following SP - it runs as a scheduled job every 10 mins. It runs fine, except the last bit that's supposed to run if the sender's email address isn't recognised. It currently does nothing - the email just sits there, unread.

CREATE PROCEDURE forum_readmail AS
Declare @hMessage varchar(255),@msg_id varchar(255),@MessageText varchar(8000),@message varchar(8000),@MessageSubject varchar(8000),@subject varchar(8000),@Origin varchar (8000),@originator_address varchar(8000)
EXEC master.dbo.xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
exec master.dbo.xp_readmail @peek='true',@msg_id=@hMessage,@message=@MessageText OUT,@subject=@MessageSubject OUT,@originator_address=@Origin OUT
IF ((SELECT COUNT(*) FROM forum_users WHERE email=@Origin)=1)
BEGIN
IF (CHARINDEX('(ThreadID=', @MessageSubject)>0) -- IF REPLYING TO EXISTING THREAD
BEGIN
DECLARE @existingID int, @em1 varchar(100), @bdy1 varchar(8000), @sbj1 varchar(500), @usr1 int, @mySubject varchar(500)

SELECT @existingID=CAST(SUBSTRING(@MessageSubject, (CHARINDEX('=', @MessageSubject)+1), (CHARINDEX(')', @MessageSubject)-(CHARINDEX('=', @MessageSubject)+1))) AS int)
SELECT @mySubject=SUBSTRING(@MessageSubject, (CHARINDEX(':', @MessageSubject)+1), CHARINDEX('(', @MessageSubject))
-- GET USERS ID FROM EMAIL
declare user_cursor1 cursor for
SELECT id from forum_users WHERE email=@Origin
open user_cursor1
fetch next from user_cursor1
into @usr1
while @@FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@MessageText, CHAR(13)+CHAR(10), '<br>'), @existingID, @usr1)
fetch next from user_cursor1
into @usr1
end
close user_cursor1
deallocate user_cursor1

--GET EMAIL LIST
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes' AND email<>@Origin
open em_cursor1
fetch next from em_cursor1
into @em1
while @@FETCH_STATUS=0
begin --CREATE EMAIL
select @sbj1='New HIS Forum Post: '+@mySubject+' (ThreadID='+cast(@existingID as varchar)+')'
select @bdy1='User '+@Origin+' has replied to a thread on the forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@MessageText
exec master.dbo.xp_sendmail
@recipients=@em1,
@message=@bdy1,
@subject=@sbj1
fetch next from em_cursor1
into @em1
end
close em_cursor1
deallocate em_cursor1
exec master.dbo.xp_deletemail @msg_id=@hMessage
END
ELSE -- IF NEW THREAD
BEGIN
DECLARE @newID int, @em2 varchar(100), @bdy2 varchar(8000), @sbj2 varchar(500), @usr2 int

-- INSERT INTO FORUM_THREADS
INSERT INTO forum_threads (subject) VALUES (@MessageSubject)
SELECT @newID=@@IDENTITY

-- GET USERS ID FROM EMAIL
declare user_cursor2 cursor for
SELECT id from forum_users WHERE email=@Origin
open user_cursor2
fetch next from user_cursor2
into @usr2
while @@FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@MessageText, CHAR(13)+CHAR(10), '<br>'), @newID, @usr2)
fetch next from user_cursor2
into @usr2
end
close user_cursor2
deallocate user_cursor2
--GET EMAIL LIST
declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes' AND email<>@Origin
open em_cursor2
fetch next from em_cursor2
into @em2
while @@FETCH_STATUS=0
begin
select @sbj2='New HIS Forum Post:'+@MessageSubject+' (ThreadID='+cast(@newID as varchar)+')'
select @bdy2='User '+@Origin+' has started a new thread on the forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@MessageText
exec master.dbo.xp_sendmail
@recipients=@em2,
@message=@bdy2,
@subject=@sbj2
fetch next from em_cursor2
into @em2
end
close em_cursor2
deallocate em_cursor2
exec master.dbo.xp_deletemail @msg_id=@hMessage
END
END
ELSE
BEGIN
exec master.dbo.xp_sendmail
@recipients=@Origin,
@message='Your email address was not recognised as being registered with the forum. Please register before emailing the forum. This is an automatically generated message. Please do not reply.',
@subject='HIS forum'
exec master.dbo.xp_deletemail @msg_id=@hMessage
END
SET @hMessage = NULL
EXEC master.dbo.xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources