IF.. ELSE IF..DECLARE, SET and EXEC

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

SQL Server Development (2000)

IF.. ELSE IF..DECLARE, SET and EXEC

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2001-06-20 : 14:52:54

I'm having a problem with this sproc and wonder if someone could kindly tell me where I've screwed up. Here's my error messages when checking syntax:

Error 156: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near the keyword 'EXEC'.

OK. So it doesn't like my IF routine structure at the bottom of the sproc. If I put the DECLARE statements at the top and remove the SET statements from the IF routines, then that error goes away. However, putting the SET statements anywhere else in the sproc generates errors. Help! (and thank you!)

CREATE PROCEDURE NotifyScripter
AS

set nocount on
DECLARE @RequestID int
DECLARE @ScripterID int
DECLARE @ScripterEmail int
DECLARE @ScripterNotified bit
DECLARE @RequesterName varchar(60)
DECLARE @RequestType char(1)
DECLARE @RequestDate datetime
DECLARE @Description varchar(140)
DECLARE NotifyCursor CURSOR FOR
SELECT R.Request_ID,
R.Scripter_ID,
RSD.Scripter_Email,
R.Scripter_Notified,
R.Requester_Name,
R.Request_Type,
R.Request_Date,
(CASE WHEN ReqCommon.Request_Type = 'S' THEN ReqScripting.Product_Name + ' ' + ReqScripting.Product_Version
WHEN ReqCommon.Request_Type = 'T' THEN ReqTesting.Product_Name + ' ' + ReqTesting.Product_Version
WHEN ReqCommon.Request_Type = 'W' THEN ReqWTS.Product_Name + ' ' + ReqWTS.Product_Version
WHEN ReqCommon.Request_Type = 'O' THEN ReqOther.ProjectDescription END) AS Description
FROM ReqCommon R
LEFT OUTER JOIN ReqScripterDef RSD ON R.Scripter_ID = RSD.Scripter_ID
LEFT OUTER JOIN ReqScripting ON R.Request_ID = ReqScripting.Request_ID
LEFT OUTER JOIN ReqTesting ON R.Request_ID = ReqTesting.Request_ID
LEFT OUTER JOIN ReqWTS ON R.Request_ID = ReqWTS.Request_ID
LEFT OUTER JOIN ReqOther ON R.Request_ID = ReqOther.Request_ID
WHERE (R.Scripter_ID <> 9) AND (R.Scripter_Notified = 0)

OPEN NotifyCursor
FETCH NEXT FROM NotifyCursor INTO @RequestID, @ScripterID, @ScripterEmail,
@ScripterNotified, @RequesterName, @RequestType, @RequestDate, @Description

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF @RequestType = 'S'
BEGIN
DECLARE @msgS varchar(1000)
SET @msgS = 'Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(10) + 'Request Date:' + CHAR(9) + CONVERT(varchar(10), @RequestDate, 101) + CHAR(10 + 'Description:' + (CHAR(9) + @Description + CHAR(13) + CHAR(13) + 'You have been assigned a new request. If you would like to view the details of this request, please click on this link (http://dboas.bpweb.bp.com/StatusDetailScripting.asp?Request_ID=' + CAST(@RequestID as varchar(10)) + ').'
EXEC master..xp_sendmail @recipients=@ScripterEmail, @subject='You have been assigned a new request', @dbuse='Staging', @message=@msgS
END

ELSE IF @RequestType = 'T'
BEGIN
DECLARE @msgT varchar(1000)
SET @msgT = 'Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(10) + 'Request Date:' + CHAR(9) + CONVERT(varchar(10), @RequestDate, 101) + CHAR(10 + 'Description:' + (CHAR(9) + @Description + CHAR(13) + CHAR(13) + 'You have been assigned a new request. If you would like to view the details of this request, please click on this link (http://dboas.bpweb.bp.com/StatusDetailTesting.asp?Request_ID=' + CAST(@RequestID as varchar(10)) + ').'
EXEC master..xp_sendmail @recipients=@ScripterEmail, @subject='You have been assigned a new request', @dbuse='Staging', @message=@msgT
END

ELSE IF @RequestType = 'O'
BEGIN
DECLARE @msgO varchar(1000)
SET @msgO = 'Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(10) + 'Request Date:' + CHAR(9) + CONVERT(varchar(10), @RequestDate, 101) + CHAR(10 + 'Description:' + (CHAR(9) + @Description + CHAR(13) + CHAR(13) + 'You have been assigned a new request. If you would like to view the details of this request, please click on this link (http://dboas.bpweb.bp.com/StatusDetailOther.asp?Request_ID=' + CAST(@RequestID as varchar(10)) + ').'
EXEC master..xp_sendmail @recipients=@ScripterEmail, @subject='You have been assigned a new request', @dbuse='Staging', @message=@msgO
END

ELSE IF @RequestType = 'W'
BEGIN
DECLARE @msgW varchar(1000)
SET @msgW = 'Request ID:' + CHAR(9) + CHAR(9) + CAST(@RequestID as varchar(10)) + CHAR(10) + 'Requester Name:' + CHAR(9) + @RequesterName + CHAR(10) + 'Request Date:' + CHAR(9) + CONVERT(varchar(10), @RequestDate, 101) + CHAR(10 + 'Description:' + (CHAR(9) + @Description + CHAR(13) + CHAR(13) + 'You have been assigned a new request. If you would like to view the details of this request, please click on this link (http://dboas.bpweb.bp.com/StatusDetailWTS.asp?Request_ID=' + CAST(@RequestID as varchar(10)) + ').'
EXEC master..xp_sendmail @recipients=@ScripterEmail, @subject='You have been assigned a new request', @dbuse='Staging', @message=@msgW
END
END

CLOSE NotifyCursor
DEALLOCATE NotifyCursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources