IF and IN - a newbie speaks...

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

Old Forums

CLOSED - General SQL Server

IF and IN - a newbie speaks...

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-07-16 : 07:32:53

With considerable help from someone here, I am now the proud owner of a bit of SQL that will read new email and drop it into an "emails" table. However, what I want to happen is this: for each email it reads, check the sender's email address - if it matches one in another table, called "forum_users" (Can you see where I'm going here? Can ya?), then it's ok to drop it into the emails table. If not, then ignore it.

I include the following SQL script - hopefully someone can point out the glaring syntax errors around that "IF" statement, and provide me with a version that works again? Please?

All help much appreciated,

Ben


Declare @hMessage varchar(255),@msg_id varchar(255) 
Declare @MessageText varchar(8000),@message varchar(8000)
Declare @MessageSubject varchar(8000),@subject varchar(8000)
Declare @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
@msg_id=@hMessage,
@message=@MessageText OUT,
@subject=@MessageSubject OUT,
@originator_address=@Origin OUT

IF @Origin IN (SELECT email FROM forum_users) THEN
	INSERT into emails (subject, body, sender) VALUES (@MessageSubject, @MessageText, @Origin)
END IF

SET @hMessage = NULL 

EXEC master.dbo.xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT 
END

drumm
Starting Member

14 Posts

Posted - 2002-07-16 : 08:02:50

Hi Ben,

I think your IF SQL statement should look something like this:

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @Origin) = 1)
BEGIN
INSERT into emails (subject, body, sender) VALUES (@MessageSubject, @MessageText, @Origin)
END

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-16 : 08:48:32

 
IF EXISTS (SELECT 1 FROM forum_users WHERE email = @Origin)
BEGIN
INSERT into emails (subject, body, sender) VALUES (@MessageSubject, @MessageText, @Origin)
END

HTH
Jasper Smith

spudhead
Starting Member

34 Posts

Posted - 2002-07-16 : 10:41:31

Wow, cheers guys. First one seems to work for me, although I don't doubt the second one would too. I've learnt something today :)

OK - this is where I get annoying. That script only does part of what I want it to do eventually. Sorry. As you've probably guessed, it's a fairly major part of a thread-based ASP email forum; and I need to do everything that you'd expect from such a monster, such as track posts accross threads and send out emails when new posts arrive.

The script below is my attempt at such a thing, and it's about at the limit of my SQL capabilities. I can get it processing incoming emails - that is; it looks for a thread ID (in the email subject), if it finds one then it creates a new thread_posts record with that thread ID, and if it doesn't then it assumes it's a new thread and... you get the rest.

That worked fine, but then I tried to add the bit that sends out details of the new post (or thread) to all my forum members, and now I keep getting the message "Incorrect syntax near the keyword 'declare'."

What am I doing wrong? Have I missed a typo? Can I not do anything else after doing an INSERT or something? Am I attempting a task that nobody in their right mind would even look at?

As ever, all offers of help and support recieved with thanks.


Declare @hMessage varchar(255),@msg_id varchar(255) 
Declare @MessageText varchar(8000),@message varchar(8000)
Declare @MessageSubject varchar(8000),@subject varchar(8000)
Declare @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
@msg_id=@hMessage,
@message=@MessageText OUT,
@subject=@MessageSubject OUT,
@originator_address=@Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @Origin) = 1)	-- IF email from forum-recognised address
BEGIN 
	IF (CHARINDEX('(ThreadID=', @MessageSubject)>0) 		-- IF email has a thread ID
		BEGIN
		DECLARE @existingID int, @em1 varchar(100), @bdy1 varchar(8000), @sbj1 varchar(500)
		SELECT @existingID=CAST(SUBSTRING(@MessageSubject, (CHARINDEX('=', @MessageSubject)+1), (CHARINDEX(')', @MessageSubject)-(CHARINDEX('=', @MessageSubject)+1))) AS int)
		INSERT INTO forum_posts (body, thread_id) VALUES (@MessageText, @existingID)

									-- Do mailing

		declare em_cursor1 cursor for
			SELECT email FROM forum_users WHERE email_option='yes'
		open em_cursor1
		fetch next from em_cursor1
		into @em1

		while @@FETCH_STATUS=0
		begin
			select @bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@MessageText
			select @sbj1='New ICNA Forum Post (ThreadID='+@existingID+')'
			exec master.dbo.xp_sendmail @em1,@bdy1,@sbj1
			fetch next from em_cursor1
			into @em1
		end
		close em_cursor1
		deallocate em_cursor1

	END
	ELSE								-- IF email has no thread ID
		BEGIN							
		DECLARE @newID int, @em2 varchar(100), @bdy2 varchar(8000), @sbj2 varchar(500)				-- Create a new thread record and use the resulting ID to add a thread_post record
		INSERT INTO forum_threads (subject) VALUES (@MessageSubject)
		SELECT @newID=@@IDENTITY
		INSERT INTO forum_posts (body, thread_id) VALUES (@MessageText, @newID

									-- Do mailing

		declare em_cursor2 cursor for
			SELECT email FROM forum_users WHERE email_option='yes'
		open em_cursor2
		fetch next from em_cursor2
		into @em2

		while @@FETCH_STATUS=0
		begin
			select @bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@MessageText
			select @sbj2='New ICNA Forum Post (ThreadID='+@newID+')'
			exec master.dbo.xp_sendmail @em2,@bdy2,@sbj2
			fetch next from em_cursor2
			into @em2
		end
		close em_cursor2
		deallocate em_cursor2

	END
END

SET @hMessage = NULL 

EXEC master.dbo.xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT 
END

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-16 : 16:43:27

You missed a bracket

 
INSERT INTO forum_posts (body, thread_id) VALUES (@MessageText, @newID)

HTH
Jasper Smith

spudhead
Starting Member

34 Posts

Posted - 2002-07-17 : 06:57:29

Doh! OK, fixed that. Cheers :)

It's still not working though :( I've got this set up as a job that runs every 3 mins - when there's no new email it runs fine. When it gets new email, it does all the database inserts and stuff but doesn't send any email back out.

It throws an error in the Job History, though:


"Syntax error converting the varchar value 'New ICNA Forum Post (ThreadID=' to a column of data type int. [SQLSTATE 22018] (Error 245).  The step failed."

I thought it might be this line:


select @sbj1='New ICNA Forum Post (ThreadID='+@existingID+')'

where I'm trying to build up a string by dropping an ID number (datatype int) into it, so I changed it to:


select @sbj1='New ICNA Forum Post (ThreadID='+CAST(@existingID AS varchar(100))+')'

But then I get an even more nasty-looking error:


ODBC error 170 (42000) Line 1: Incorrect syntax near 'Forum'. [SQLSTATE 01000] (Message 18014).

Thing is though, it repeats that error 4 times - which suggests I'm doing something right, as there's currently 4 users in the forum_users table. And sure enough, it tells me that the job succeeded - although it's still not sending emails.

Is this another mindless typo?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources