help finishing query - 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 2005 Forums

Transact-SQL (2005)

help finishing query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-15 : 10:12:24

Hi,

Im modifying a SPROC I have, and am wondering if anyone can help me modify the code efficiently.

Currently the INSERT SP is just a simple procedure that inserts a users message to another user (social networking). We want to advance it by adding instant email alerts to the receiving user, but the receiving user has some settings to configure in order to receive them.

We return the value to the web app via an output param, and then do the appropriate command in the web app.

I have the code below, and am not sure how exactly to write it best. I think all of the logic is here, I am just haveing some probs with writing efficient SQL control structure...

any help is much appreciated !!

thanks once again :)
mike123

DECLARE @send_ReceivedMail TINYINT
SELECT @send_ReceivedMail = send_ReceivedMail FROM tblnotification_Settings WHERE userID = @MessageTo

--we have set a value for @send_ReceivedMail above, depending on the value, lets take the following steps as shown below

--0 don't send user email (SELECT @sendNotification = 0)
--1 send only if I am online (SELECT @sendNotification = 0 if userID not found in tblActiveUsers, SELECT @sendNotification = 1 if it is found)
--2 send always (SELECT @sendNotification = 1)

DECLARE @messageTo_Online TINYINT

if exists (select userID from tblActiveUsers where userID = @MessageFrom))
begin

end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 10:23:11

[code]-- Peso 1
DECLARE @SendMail TINYINT

SELECT @SendMail = send_ReceivedMail
FROM tblNotification_Settings
WHERE userID = @MessageTo

IF @SendMail NOT IN (1, 2) OR @SendMail IS NULL
RETURN

IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActiveUsers WHERE userID = @MessageFrom)
BEGIN
... send email here
END

-- Peso 2
DECLARE @SendMail TINYINT

SELECT @SendMail = send_ReceivedMail
FROM tblNotification_Settings
WHERE userID = @MessageTo

IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActiveUsers WHERE userID = @MessageFrom AND @SendMail = 1)
BEGIN
... send email here
END[/code]

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-15 : 10:27:32

Hey Peso,

Excellent! I knew there was a smart way to do it, I am using solution #2 as its reads easier than #1 for me personally.

Thanks once again! :)
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-15 : 10:49:54

Sorry, can't seem to get the exact syntax down when integrating a second command.. Your code runs perfectly, but when I add the below code underneath it messes up

much appreciated once again

thanks :)
mike123

IF @replyToID <> 0
BEGIN
UPDATE tblmessage SET checked = 2 WHERE messageID = @replyToID
SELECT @blocked = 0
END

SELECT @SendMail = send_ReceivedMail
FROM tblNotification_Settings
WHERE userID = @MessageTo

IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActiveUsers WHERE userID = @MessageFrom AND @SendMail = 1)
BEGIN
... send email here
END

IF @replyToID <> 0
BEGIN
UPDATE tblmessage SET checked = 2 WHERE messageID = @replyToID
SELECT @blocked = 0
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 11:44:09

Any error message(s)?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-15 : 16:02:15

Hey Peso,

Sure , here is the whole thing.. I have hilited row 115 and 119

Error is :

Msg 156, Level 15, State 1, Procedure insert_Message_2, Line 115
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Procedure insert_Message_2, Line 119
Incorrect syntax near 'end'.

Very much appreciated!

Thanks once again! :)
mike123

CREATE PROCEDURE [dbo].[insert_Message_2]

(
@MessageFrom [int],
@MessageTo [int],
@Message [varchar](2500),
@prevMessage [varchar](2500),
@Subject [varchar](100),
@replyToID [int],
@IP [varchar](15),
@blocked [tinyint] = 0 OUTPUT,
@sendNotification [tinyint] = 0 OUTPUT
)
AS SET NOCOUNT ON

SELECT userID from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom

IF @@Rowcount = 0
begin

if exists (select keyword from tblSpam_Keywords where charindex(keyword, @message) > 0)
begin
-- HERE WE CAN CHANGE HOW ITS RECEIVED IN THE MAILBOX, LETS MAKE IT ARRIVE (CHECKED,DELETED)
INSERT INTO [dbo].[tblMessage]
([MessageFrom],
[MessageTo],
[Message],
[prevMessage],
[Subject],
[date],
[IP],
[Checked],
[deletedBySender],
[deletedByRecipient]
)

VALUES
(@MessageFrom,
@MessageTo,
@Message,
@prevMessage,
@Subject,
getDate(),
@IP,
1,
0,
1)

DECLARE @offendingID int
SELECT @offendingID = SCOPE_IDENTITY()

INSERT INTO tblSpamReports_KeyWord_Violations(offendingID, spamTypeID, dateViolated, reportCleared) VALUES (@offendingID, 2, getDate(), 0)

SELECT @sendNotification = 0

end
else
begin
--print '@message does not contains spam'
INSERT INTO [dbo].[tblMessage]
([MessageFrom],
[MessageTo],
[Message],
[prevMessage],
[Subject],
[date],
[IP],
[Checked],
[deletedBySender],
[deletedByRecipient]
)

VALUES
(@MessageFrom,
@MessageTo,
@Message,
@prevMessage,
@Subject,
getDate(),
@IP,
0,
0,
0)

----we have set a value for @send_ReceivedMail above, depending on the value, lets take the following steps as shown below
----0 don't send user email (SELECT @sendNotification = 0)
----1 send only if I am online (SELECT @sendNotification = 0 if userID not found in tblActiveUsers, SELECT @sendNotification = 1 if it is found)
----2 send always (SELECT @sendNotification = 1)

DECLARE @SendMail TINYINT
SELECT @SendMail = send_ReceivedMail FROM tblNotification_Settings WHERE userID = @MessageTo

IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActive_Users WHERE userID = @MessageFrom AND @SendMail = 1)
BEGIN
SELECT @sendNotification = 1
END

IF @replyToID <> 0
BEGIN
UPDATE tblmessage SET checked = 2 WHERE messageID = @replyToID
SELECT @blocked = 0
END
end
ELSE
begin
SELECT @blocked = 1
SELECT @sendNotification = 0
end

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 16:14:42

Works for me, with a little help of proper indentation of code...

CREATE PROCEDURE [dbo].[insert_Message_2] 
(
	@MessageFrom [int],
	@MessageTo [int],
	@Message [varchar](2500),
	@prevMessage [varchar](2500),
	@Subject [varchar](100),
	@replyToID [int],
	@IP [varchar](15),
	@blocked [tinyint] = 0 OUTPUT,
	@sendNotification [tinyint] = 0 OUTPUT 
)
AS

SET NOCOUNT ON

IF EXISTS (SELECT * from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom)
	RETURN

if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')
	begin
		-- HERE WE CAN CHANGE HOW ITS RECEIVED IN THE MAILBOX, LETS MAKE IT ARRIVE (CHECKED,DELETED)
		DECLARE @offendingID int

		INSERT INTO [dbo].[tblMessage] 
			(
				[MessageFrom],
				[MessageTo],
				[Message],
				[prevMessage],
				[Subject],
				[date],
				[IP],
				[Checked],
				[deletedBySender],
				[deletedByRecipient]
			) 
		VALUES	(
				@MessageFrom,
				@MessageTo,
				@Message,
				@prevMessage,
				@Subject,
				getDate(),
				@IP,
				1,
				0,
				1
			)

		SET @offendingID = SCOPE_IDENTITY()

		INSERT INTO tblSpamReports_KeyWord_Violations
			(
				offendingID,
				spamTypeID,
				dateViolated,
				reportCleared
			)
		VALUES	(
				@offendingID, 
				2, 
				getDate(), 
				0
			)

		SET @sendNotification = 0
	end
else
	begin
		--print '@message does not contains spam'
		INSERT INTO [dbo].[tblMessage] 
			(
				[MessageFrom],
				[MessageTo],
				[Message],
				[prevMessage],
				[Subject],
				[date],
				[IP],
				[Checked],
				[deletedBySender],
				[deletedByRecipient]
			) 
		VALUES	(
				@MessageFrom,
				@MessageTo,
				@Message,
				@prevMessage,
				@Subject,
				getDate(),
				@IP,
				0,
				0,
				0
			)

		----we have set a value for @send_ReceivedMail above, depending on the value, lets take the following steps as shown below
		----0 don't send user email (SELECT @sendNotification = 0)
		----1 send only if I am online (SELECT @sendNotification = 0 if userID not found in tblActiveUsers, SELECT @sendNotification = 1 if it is found)
		----2 send always (SELECT @sendNotification = 1)
		DECLARE @SendMail TINYINT
		
		SELECT	@SendMail = send_ReceivedMail
		FROM	tblNotification_Settings
		WHERE	userID = @MessageTo

		IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActive_Users WHERE userID = @MessageFrom AND @SendMail = 1)
			BEGIN
				SET @sendNotification = 1
			END

		IF @replyToID <> 0 
			BEGIN
				UPDATE	tblmessage
				SET	checked = 2
				WHERE	messageID = @replyToID

				SET	@blocked = 0 
			END
	end
ELSE 
	begin 
		SELECT	@blocked = 1, 
			@sendNotification = 0
	end

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 04:48:59

Hey Peso,

Much Cleaner :) With your code right there I am down to 1 error, instead of 2. I am still really confused as to why I am getting an error ? :S

It is on line 130, which is near the bottom

IF @replyToID <> 0

Msg 156, Level 15, State 1, Procedure insert_Message_2, Line 130
Incorrect syntax near the keyword 'ELSE'.

Again any help much appreciated can't wait to get this one over with! :)

thanks,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 04:52:38

I can see there are 2 ELSE...

Is the last part

ELSE 
	begin 
		SELECT	@blocked = 1, 
			@sendNotification = 0
	end

related to the "IF @replyToID <> 0" or "if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')
"?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 04:57:39

Hey Peso,

It is supposed to be related to the "if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')" part :)

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 05:05:02

Then I think you need this change

-- Before
		IF @replyToID <> 0 
			BEGIN
				UPDATE	tblmessage
				SET	checked = 2
				WHERE	messageID = @replyToID

				SET	@blocked = 0 
			END
	end
ELSE 
	begin 
		SELECT	@blocked = 1, 
			@sendNotification = 0
	end

-- After
		IF @replyToID <> 0 
			BEGIN
				UPDATE	tblmessage
				SET	checked = 2
				WHERE	messageID = @replyToID

				SET	@blocked = 0 
			END
		ELSE 
			begin 
				SELECT	@blocked = 1, 
					@sendNotification = 0
			end
	end

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 05:18:11

Hey Peso,

This code doesn't error out, but to me it looks like


				begin 
					SELECT	@blocked = 1, 
						@sendNotification = 0
				end

this code is run if @replyToID <> 0 ?

I want this code to run if the below condition is met, in case my previous reply was unclear. I will repost my whole SP again to be more clear.

if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')

THX!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 05:19:16

[code]
CREATE PROCEDURE [dbo].[insert_Message_2]
(
@MessageFrom [int],
@MessageTo [int],
@Message [varchar](2500),
@prevMessage [varchar](2500),
@Subject [varchar](100),
@replyToID [int],
@IP [varchar](15),
@blocked [tinyint] = 0 OUTPUT,
@sendNotification [tinyint] = 0 OUTPUT
)
AS

SET NOCOUNT ON

IF EXISTS (SELECT * from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom)
RETURN

if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')
begin
-- HERE WE CAN CHANGE HOW ITS RECEIVED IN THE MAILBOX, LETS MAKE IT ARRIVE (CHECKED,DELETED)
DECLARE @offendingID int

INSERT INTO [dbo].[tblMessage]
(
[MessageFrom],
[MessageTo],
[Message],
[prevMessage],
[Subject],
[date],
[IP],
[Checked],
[deletedBySender],
[deletedByRecipient]
)
VALUES (
@MessageFrom,
@MessageTo,
@Message,
@prevMessage,
@Subject,
getDate(),
@IP,
1,
0,
1
)

SET @offendingID = SCOPE_IDENTITY()

INSERT INTO tblSpamReports_KeyWord_Violations
(
offendingID,
spamTypeID,
dateViolated,
reportCleared
)
VALUES (
@offendingID,
2,
getDate(),
0
)

SET @sendNotification = 0
end
else
begin
--print '@message does not contains spam'
INSERT INTO [dbo].[tblMessage]
(
[MessageFrom],
[MessageTo],
[Message],
[prevMessage],
[Subject],
[date],
[IP],
[Checked],
[deletedBySender],
[deletedByRecipient]
)
VALUES (
@MessageFrom,
@MessageTo,
@Message,
@prevMessage,
@Subject,
getDate(),
@IP,
0,
0,
0
)

----we have set a value for @send_ReceivedMail above, depending on the value, lets take the following steps as shown below
----0 don't send user email (SELECT @sendNotification = 0)
----1 send only if I am online (SELECT @sendNotification = 0 if userID not found in tblActiveUsers, SELECT @sendNotification = 1 if it is found)
----2 send always (SELECT @sendNotification = 1)
DECLARE @SendMail TINYINT

SELECT @SendMail = send_ReceivedMail
FROM tblNotification_Settings
WHERE userID = @MessageTo

IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActive_Users WHERE userID = @MessageFrom AND @SendMail = 1)
BEGIN
SET @sendNotification = 1
END

IF @replyToID <> 0
BEGIN
UPDATE tblmessage
SET checked = 2
WHERE messageID = @replyToID

SET @blocked = 0
END
ELSE
begin
SELECT @blocked = 1,
@sendNotification = 0
end
end

[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 05:35:25

eek maybe my logic is completely wrong here..

SELECT @blocked = 1, @sendNotification = 0

Is supposed to return when the user is blocked, which is the very first statement, but I am thinking it never gets to this line of code because of the RETURN ?

IF EXISTS (SELECT * from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom)
RETURN

perhaps I should remove this ELSE at the bottom of my SP, and change the top to

IF EXISTS (SELECT * from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom)
SELECT @blocked = 1, @sendNotification = 0
RETURN

might have just uncovered a pretty huge but which went unnoticed, obviously with undesirable results :S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 05:45:17

Yes butg.

CREATE PROCEDURE [dbo].[insert_Message_2] 
(
	@MessageFrom [int],
	@MessageTo [int],
	@Message [varchar](2500),
	@prevMessage [varchar](2500),
	@Subject [varchar](100),
	@replyToID [int],
	@IP [varchar](15),
	@blocked [tinyint] = 0 OUTPUT,
	@sendNotification [tinyint] = 0 OUTPUT 
)
AS

SET NOCOUNT ON

IF EXISTS (SELECT * from tblBlockList WHERE userID = @messageTo and BlockUserID = @MessageFrom)
	begin 
		SELECT	@blocked = 1, 
			@sendNotification = 0

		RETURN
	end

if exists (select keyword from tblSpam_Keywords where @message like '%' + keyword + '%')
	begin
		-- HERE WE CAN CHANGE HOW ITS RECEIVED IN THE MAILBOX, LETS MAKE IT ARRIVE (CHECKED,DELETED)
		DECLARE @offendingID int

		INSERT INTO [dbo].[tblMessage] 
			(
				[MessageFrom],
				[MessageTo],
				[Message],
				[prevMessage],
				[Subject],
				[date],
				[IP],
				[Checked],
				[deletedBySender],
				[deletedByRecipient]
			) 
		VALUES	(
				@MessageFrom,
				@MessageTo,
				@Message,
				@prevMessage,
				@Subject,
				getDate(),
				@IP,
				1,
				0,
				1
			)

		SET @offendingID = SCOPE_IDENTITY()

		INSERT INTO tblSpamReports_KeyWord_Violations
			(
				offendingID,
				spamTypeID,
				dateViolated,
				reportCleared
			)
		VALUES	(
				@offendingID, 
				2, 
				getDate(), 
				0
			)

		SET @sendNotification = 0
	end
else
	begin
		--print '@message does not contains spam'
		INSERT INTO [dbo].[tblMessage] 
			(
				[MessageFrom],
				[MessageTo],
				[Message],
				[prevMessage],
				[Subject],
				[date],
				[IP],
				[Checked],
				[deletedBySender],
				[deletedByRecipient]
			) 
		VALUES	(
				@MessageFrom,
				@MessageTo,
				@Message,
				@prevMessage,
				@Subject,
				getDate(),
				@IP,
				0,
				0,
				0
			)

		----we have set a value for @send_ReceivedMail above, depending on the value, lets take the following steps as shown below
		----0 don't send user email (SELECT @sendNotification = 0)
		----1 send only if I am online (SELECT @sendNotification = 0 if userID not found in tblActiveUsers, SELECT @sendNotification = 1 if it is found)
		----2 send always (SELECT @sendNotification = 1)
		DECLARE @SendMail TINYINT
		
		SELECT	@SendMail = send_ReceivedMail
		FROM	tblNotification_Settings
		WHERE	userID = @MessageTo

		IF @SendMail = 2 OR EXISTS (SELECT * FROM tblActive_Users WHERE userID = @MessageFrom AND @SendMail = 1)
			BEGIN
				SET @sendNotification = 1
			END

		IF @replyToID <> 0 
			BEGIN
				UPDATE	tblmessage
				SET	checked = 2
				WHERE	messageID = @replyToID

				SET	@blocked = 0 
			END
	end

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-06-17 : 05:58:19

Colossal bug!! =[ , very glad its been found!!.

now inspecting full damage thats been done

thanks again!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources