how to pass an expression in a stored procedure

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

how to pass an expression in a stored procedure

Author

Topic

scottiesharpe
Starting Member

2 Posts

Posted - 2010-09-27 : 18:35:39

On SQL 2005 Workgroup Edition...

I'm using the following SP to send a simple text automated birthday
notification reminder to my sales staff each morning. When a customer's
birthdate month and day are matched to getdate() it uses Database Mail to
send the mail. It all seems to work.

The problem I am having is I want the body of the email to be an expression
some like:

SET @htmlbody = 'Your customer ' + @name + ' is having a birthday today.
Please call them at ' + @phone + ' and wish them a happy Birthday!'

When I run this SP, the email that is sent has nothing in the body. It's
just blank.

********************** BEGIN QUOTE ************************

DECLARE
@name VARCHAR(max),
@birthdate datetime,
@email NVARCHAR(max),
@phone NVARCHAR(max),
@htmlbody NVARCHAR(max)

SET @htmlbody = 'Your customer ' + @name + ' is having a birthday today.
Please call them at ' + @phone + ' and wish them a happy Birthday!'

DECLARE C1 CURSOR READ_ONLY
FOR
select name,birthdate,email,phone from customers

OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email,@phone
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email,
@subject = 'Birthday Today Notification Reminder',
@body_format = 'HTML',
@body = @htmlbody
END

FETCH NEXT FROM C1 INTO
@name, @birthdate, @email,@phone

END
CLOSE C1
DEALLOCATE C1

********************** END OF QUOTE ***********************

When I change the body param to

@body = 'Test Message'

or even

@body = @name

the body of the email works. It appears with the 'Test Message' or name value,
respectively.

So it seems like something about my @htmlbody expression is not working and I
can't figure out what it is. Does anyone have any suggestions? Thank you in
advance for your help!

Scottie Sharpe
GoldMine CRM Consultant

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-27 : 18:41:34

Can @name or @phone be NULL? If so, then that's the issue.

You can use CONCAT_NULL_YIELDS_NULL option, ISNULL, or COALESCE functions to resolve it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-09-27 : 20:05:04

(1) anytime you are concatenating two or more parameters you need to check for NULLs for every parameter as NULL in any of them can NULLify the entire concatenation.
(2) If you have to use cursor, try using LOCAL, FORWARD_ONLY parameters too.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scottiesharpe
Starting Member

2 Posts

Posted - 2010-09-29 : 19:30:03

Thanks! Great suggestions...I did not know that.

However that was not the issue. The issue was my own fault. I put the SET @htmlbody = xxxxxx line in the wrong spot. It should be INSIDE the fetch loop so that it gets rebuilt each time a new record is fetched.

DECLARE
@name VARCHAR(max),
@birthdate datetime,
@email NVARCHAR(max),
@phone NVARCHAR(max),
@htmlbody NVARCHAR(max)

DECLARE C1 CURSOR READ_ONLY
FOR
select name,birthdate,email,phone from customers

OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email,@phone
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())

SET @htmlbody = 'Your customer ' + @name + ' is having a birthday today.
Please call them at ' + @phone + ' and wish them a happy Birthday!'

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email,
@subject = 'Birthday Today Notification Reminder',
@body_format = 'HTML',
@body = @htmlbody
END

FETCH NEXT FROM C1 INTO
@name, @birthdate, @email,@phone

END
CLOSE C1
DEALLOCATE C1

Scottie Sharpe
GoldMine CRM Consultant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources