September 22, 2010 at 8:13 am
I have an SP that runs and updates a column in the database. I setup a dbmail profile and everything and it works fine, unless I try to set the @body parameter to a variable that created within the procedure. Can someone please let me know what I am missing? Thanks!
This is the non-functioning code. Email gets fired but the body is blank. If I check the status of the email it shows the body as NULL. Now if I hard code text as the value for @body, it works fine:
alter PROCEDURE [dbo].[DEV_EventReg_nmram] @eventregid varchar(255) --uniqueidentifier
AS
declare @regid uniqueidentifier
declare @date as datetime
begin
SET NOCOUNT ON;
set @date = getdate()
set @regid = cast(@eventregid as uniqueidentifier)
declare @compname varchar(255)
select @compname = new_companyname from mytable where registrationid = @regid
declare @bodytext as varchar(MAX)
set @bodytext = 'Non member registered as member: ' + @compname + '. Please check their registration record and be sure to follow up with them.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Myprofile',
@recipients = 'myemail@mydomain.org',
@subject = '!!!!!!!!!!!!Non-Member Registered as Member!!!!!!!!!!!!!',
@body = @bodytext
UPDATE mytable
set mycolumn = 1
where registrationid = @regid -- @eventregid
Table/column names have been changed to protect the innocent. Thanks in advance!! 😀
September 22, 2010 at 9:10 am
Hi Mike
Is it possible that @compname has a NULL value?
If you concatenate a NULL to a string the string = NULL.
Try something like
SELECT 'my string' + 'my other string'
SELECT 'my string' + 'my other string' + NULL
You could handle this easily with ISNULL(@compname, 'insert something here')
Maxim
September 22, 2010 at 9:13 am
Maxim, you are awesome. Never even occurred to me to check that because I was so sure that there was definately a company name in that variable. Sure enough, put an isnull around it and it worked fine and displayed the isnull default value. Thanks for the input -- now I know where the problem is and can correct it. Big help!
September 22, 2010 at 9:23 am
My pleasure, we all get caught by the absorbing nature of the NULL at some point! 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply