CASE or IF 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

General SQL Server Forums

New to SQL Server Programming

CASE or IF Statement

Author

Topic

Matt-21
Starting Member

11 Posts

Posted - 2010-11-15 : 06:07:36

I am an SQL novice that is struggling to write a trigger that emails specific users based on an inserted value in a simple sql table. The code works when I use an if statement and I am handling only 1 value. When I try to handle several values (in my case names and email addresses) although I have no errors the trigger does not email the correct people. After searching the web it seems I should use the CASE statement for multiple values however I am now getting syntax errors. Any help would be much appreciated. Heres my Trigger;

ALTER TRIGGER [dbo].[newChangeCtrl] ON [dbo].[it_changectrl] AFTER INSERT AS

DECLARE @Type Varchar(50)
DECLARE @Device Varchar(50)
DECLARE @Reason Varchar(50)
DECLARE @RequestedBy Varchar(30)
DECLARE @ApprovedBy Varchar(5)
DECLARE @Status Varchar(30)
DECLARE @Description Varchar(500)
DECLARE @Assigned Varchar(30)
DECLARE @MailTo NVARCHAR(30)
DECLARE @MailCCTo NVARCHAR(30)

SET @Type = (SELECT changeitem FROM inserted)
SET @Device = (SELECT changetype FROM inserted)
SET @Reason = (SELECT changereason FROM inserted)
SET @RequestedBy = (SELECT requestedby FROM inserted)
SET @ApprovedBy = (SELECT approvedby FROM inserted)
SET @Status = (SELECT status FROM inserted)
SET @Description = (SELECT changedescription FROM inserted)
SET @Assigned = (SELECT assignedto FROM inserted)

CASE @Assigned
WHEN 'MWT'
THEN SET @MailTo = N'me@myco.co.uk'
SET @MailCCTo = N'alt@email.co.uk';
WHEN 'AM'
THEN SET @MailTo = N'am@myco.co.uk'
SET @MailCCTo = N'support@myco.co.uk; me@myco.co.uk';
WHEN 'MSOL'
THEN SET @MailTo = N'support@myco.co.uk'
SET @MailCCTo = N'support@myco.co.uk; me@myco.co.uk';
WHEN 'JV'
THEN SET @MailTo = N'jv@myco.co.uk'
SET @MailCCTo = N'am@myco.co.uk; me@myco.co.uk';
WHEN 'JM'
THEN SET @MailTo = N'jm@myco.co.uk’
SET @MailCCTo = N'am@myco.co.uk; me@myco.co.uk';
WHEN 'SPW'
THEN SET @MailTo = N'spw@myco.co.uk'
SET @MailCCTo = N'me@myco.co.uk';
WHEN 'SSF'
THEN SET @MailTo = N'me@myco.co.uk'
SET @MailCCTo = N'spw@myco.co.uk';

ELSE SET @MailTo = N'me@myco.co.uk'
SET @MailCCTo = N'spw@myco.co.uk';

END CASE

BEGIN

DECLARE @msg varchar(3000)
SET @msg = 'Dear '+ @Assigned + Char(13) + Char(10)+'A new entry has been added to the MyCo Change Control and has been assigned to you. Please update your progress by logging on to http://changelog from anywhere on the MyCo Network. The case details are as follows;' + Char(13) + Char(10)+ + Char(13) + Char(10)+
'Type: ' + @Type + Char(13) + Char(10) +
'Device: ' + @Device + Char(13) + Char(10) +
'Reason: ' + @Reason + Char(13) + Char(10) +
'Requested By: ' + @RequestedBy + Char(13) + Char(10) +
'Approved By: ' + @ApprovedBy + Char(13) + Char(10) +
'Description. ' + @Description + Char(13) + Char(10) +
Char(13) + Char(10) +
'Best Regards' + Char(13) + Char(10) +
'MyCo DB Administration'

--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients= @MailTo, @copy_recipients= @MailCCTo, @body= @msg, @subject = 'New IT Support', @profile_name = 'DBMailProfile'

END

Matt

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-15 : 06:11:14

Your trigger is assuming that only one record at a time is being inserted.

e.g. what happens to SET @Type = SET @Assigned = (SELECT assignedto FROM inserted) if you insert 2 or more records at a time? It could cause the symptoms you describe i.e. emailing the wrong person.

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

Matt-21
Starting Member

11 Posts

Posted - 2010-11-16 : 05:11:41

Thanks elwoos
However I'm not sure I understand. Currently 1 full record is been entered everytime. This isn't my problem although it seems it may cause one later down the line. I only get the emails going to the wrong person when I use the IF statement. The CASE statement is a syntax error, in fact this is the exact error

Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 34
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 38
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 41
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 44
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 47
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 50
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 53
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 57
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure newChangeCtrl, Line 60
Incorrect syntax near the keyword 'CASE'.

Perhaps from what your saying I need to totally re-write it but should I be using CASE or IF? and can you tell me where I am going wrong with my CASE syntax?

Many Thanks

Matt

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-16 : 07:14:46

First a few minor syntax things. You don't need the semi colon at the end of the SQL statements.

For some reason the last character on the line

WHEN 'JM'
THEN SET @MailTo = N'jm@myco.co.uk’

is not what it looks like, it should be

WHEN 'JM'
THEN SET @MailTo = N'jm@myco.co.uk'

There is no reason you can't use an if statement in your trigger.

If I understand correctly this should work

...
IF @Assigned = 'MWT'
BEGIN
SET @MailTo = N'me@myco.co.uk'
SET @MailCCTo = N'alt@email.co.uk'
END
ELSE
IF @Assigned = 'AM'
BEGIN
SET @MailTo = N'am@myco.co.uk'
SET @MailCCTo = N'support@myco.co.uk; me@myco.co.uk'
END
...

Is that what your original code looked like?

If you would prefer to use a case statement then I think you would need two. e.g.

SELECT @MailTo =
CASE @Assigned
WHEN 'MWT' THEN N'me@myco.co.uk'
WHEN 'AM' THEN N'me@myco.co.uk'
WHEN 'MSOL' THEN N'support@myco.co.uk'
WHEN 'JV' THEN N'jv@myco.co.uk'
WHEN 'JM' THEN N'jm@myco.co.uk'
WHEN 'SPW' THEN N'spw@myco.co.uk'
WHEN 'SSF' THEN N'me@myco.co.uk'
ELSE N'me@myco.co.uk'
END

and something similar for @MailCCTo

If this doesn't resolve the issue can you please provide some ddl, sample data etc as described here - http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

steve

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

Matt-21
Starting Member

11 Posts

Posted - 2010-11-16 : 12:18:59

Thanks Steve
You have understood my requirement exactly and yes my if statement was exactly as you suggest. The CASE statement still errored but it seems from what you say it the IF statement is OK.
State the question

"Why does my Trigger email the wrong people". I enclose the DDL of my table for a simple change log.

Please post the DDL of your tables (Including Indexes, and constraints)

CREATE TABLE [dbo].[it_changectrl](
[changerequest] [int] IDENTITY(1,1) NOT NULL,
[changeitem] [nchar](50) COLLATE Latin1_General_BIN NULL,
[changetype] [nchar](50) COLLATE Latin1_General_BIN NULL,
[changereason] [nchar](50) COLLATE Latin1_General_BIN NULL,
[changedescription] [nchar](500) COLLATE Latin1_General_BIN NULL,
[changeresolution] [nchar](500) COLLATE Latin1_General_BIN NULL,
[requestedby] [nchar](30) COLLATE Latin1_General_BIN NULL,
[approvedby] [nchar](5) COLLATE Latin1_General_BIN NULL,
[assignedto] [nchar](30) COLLATE Latin1_General_BIN NULL,
[mscaseno] [nchar](15) COLLATE Latin1_General_BIN NULL,
[status] [nchar](10) COLLATE Latin1_General_BIN NULL,
[createddate] [datetime] NOT NULL CONSTRAINT [DF__it_change__creat__7246E95D] DEFAULT (getdate()),
[approveddate] [datetime] NULL,
CONSTRAINT [PK_it_changectrl_1] PRIMARY KEY CLUSTERED
(
[changerequest] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

4. Post whatever DML that you have attempted already...

ALTER TRIGGER [dbo].[newChangeCtrl] ON [dbo].[it_changectrl] AFTER INSERT AS

DECLARE @Type Varchar(50)
DECLARE @Device Varchar(50)
DECLARE @Reason Varchar(50)
DECLARE @RequestedBy Varchar(30)
DECLARE @ApprovedBy Varchar(5)
DECLARE @Status Varchar(30)
DECLARE @Description Varchar(500)
DECLARE @Assigned Varchar(30)
DECLARE @MailTo NVARCHAR(30)
DECLARE @MailCCTo NVARCHAR(30)

SET @Type = (SELECT changeitem FROM inserted)
SET @Device = (SELECT changetype FROM inserted)
SET @Reason = (SELECT changereason FROM inserted)
SET @RequestedBy = (SELECT requestedby FROM inserted)
SET @ApprovedBy = (SELECT approvedby FROM inserted)
SET @Status = (SELECT status FROM inserted)
SET @Description = (SELECT changedescription FROM inserted)
SET @Assigned = (SELECT assignedto FROM inserted)

IF @Assigned = 'MWT'
BEGIN

SET @MailTo = N'mwt@myco.co.uk'
Set @MailCCTo = N'mwt@xyz.co.uk'
END

ELSE

IF @Assigned = 'AM'
BEGIN

SET @MailTo = N'am@myco.co.uk' .co.uk'
Set @MailCCTo = N'support@myco.co.uk' ; mwt@myco.co.uk'
END

ELSE

IF @Assigned = 'MSOL'

BEGIN
SET @MailTo = N'support@myco.co.uk'
Set @MailCCTo = N'am@myco.co.uk' ; mwt@myco.co.uk'
END

ELSE

IF @Assigned = 'JV'
BEGIN

SET @MailTo = N'jv@myco.co.uk'
Set @MailCCTo = N'am@myco.co.uk' ; support@myco.co.uk' ; mwt@myco.co.uk'
END

ELSE

IF @Assigned = 'JM'
BEGIN

SET @MailTo = N'jm@myco.co.uk'
Set @MailCCTo = N'am@myco.co.uk' ; support@myco.co.uk' ; mwt@myco.co.uk'
END

ELSE

IF @Assigned = 'SPW'
BEGIN

SET @MailTo = N'spw@myco.co.uk'
Set @MailCCTo = N'mwt@myco.co.uk'
END

ELSE

IF @Assigned = 'SSF'
BEGIN

SET @MailTo = N'mwt@myco.co.uk'
Set @MailCCTo = N'spw@myco.co.uk'
END

ELSE

IF @Assigned = 'Other'
BEGIN

SET @MailTo = N'mwt@myco.co.uk'
Set @MailCCTo = N'spw@myco.co.uk'

END

begin

DECLARE @msg varchar(3000)
SET @msg = 'Dear '+ @Assigned + Char(13) + Char(10)+'A new entry has been added to the SSF Change Control and has been assigned to you. Please update your progress by logging on to http://changelog from anywhere on the SSF Network. The case details are as follows;' + Char(13) + Char(10)+ + Char(13) + Char(10)+ 'Type: ' + @Type + Char(13) + Char(10) +
'Device: ' + @Device + Char(13) + Char(10) +
'Reason: ' + @Reason + Char(13) + Char(10) +
'Requested By: ' + @RequestedBy + Char(13) + Char(10) +
'Approved By: ' + @ApprovedBy + Char(13) + Char(10) +
'Description. ' + @Description + Char(13) + Char(10) +
Char(13) + Char(10) +
'Best Regards' + Char(13) + Char(10) +
'SSF DB Administration'

--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients= @MailTo, @copy_recipients= @MailCCTo, @body= @msg, @subject = 'New IT Support', @profile_name = 'DBMailProfile'

END

5. Post the expected results

I expect the the code to look at the inserted value of the field assignedto and email the person (represented by the value of assignedto) what change/issue has been reported and the status and description of the change/issue. The trigger works fine when there is only one person in the list adding multiple people and multiple address' has caused no syntax errors but it does not email the correct people.

Matt

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-16 : 12:25:18

quote:
Originally posted by elwoos

First a few minor syntax things. You don't need the semi colon at the end of the SQL statements.

I agree that they are not needed for all statements (they are with the WITH clause). They will be required in a future version of SQL. So, it's a good habbit to get into.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-17 : 07:09:42

Lamprey you are perfectly correct, if I remember rightly the semi colon is part of standard SQL, its certainly used in Postgres for example. Personally I would prefer it to be dumped universally rather than adopted but what do I know.

Matt

First the bad news

You have a few typos in the mailCCTo assignements e.g.

IF @Assigned = 'JV'
BEGIN

SET @MailTo = N'jv@myco.co.uk'
Set @MailCCTo = N'am@myco.co.uk' ; support@myco.co.uk' ; mwt@myco.co.uk'
END

should be

IF @Assigned = 'JV'
BEGIN

SET @MailTo = N'jv@myco.co.uk'
Set @MailCCTo = N'am@myco.co.uk ; support@myco.co.uk ; mwt@myco.co.uk'
END

there are a few occurences of this and I am not sure if it's just something that got lost during copy/paste

Another minor issue. Most of your variable declarations are varchar yet the table is mostly nchar. So I think your declarations should be

DECLARE @Type nchar(50)
DECLARE @Device nchar(50)
DECLARE @Reason nchar(50)
DECLARE @RequestedBy nchar(30)
DECLARE @ApprovedBy nchar(5)
DECLARE @Status nchar(10)
DECLARE @Description nchar(500)
DECLARE @Assigned nchar(30)

In particular it appears that status should be 10 characters not 30.

it also looks to me as though these two may have been mixed up

SET @Type = (SELECT changeitem FROM inserted)
SET @Device = (SELECT changetype FROM inserted)

i.e. I think they should be

SET @Device = (SELECT changeitem FROM inserted)
SET @Type = (SELECT changetype FROM inserted)

Now the good news, I think the problem you are having is a very simple one. The email addresses that you are sending to are being truncated e.g. you have

DECLARE @MailCCTo NVARCHAR(30)

yet you have for example
SET @MailCCTo = N'support@myco.co.uk; me@myco.co.uk';

which will mean @MailCCTo is "support@myco.co.uk; me@myco.co" which I am pretty sure is not what you intended.

Steve

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

Matt-21
Starting Member

11 Posts

Posted - 2010-11-22 : 07:02:26

The trigger works!!! Thanks so much for the help

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources