asked on

SQL Creating email subject with field values in it

I want to make the subject contain the @OrderNo and @Product,

e.g. Email Alert for Order No: @OrderNo , Product:@Product ,

i.e. Email Alert for Order No: 911283 , Product: GE101

what i was thinking was to declare @SubjectText and somehow create the text that way and then the @subject parameter as

@subject = @SubjectTest

DECLARE @Cost FLOAT
DECLARE @sql VARCHAR(1000)
DECLARE @Product CHAR(20)
DECLARE @OrderNo CHAR(10)
DECLARE @OrderLineNo CHAR(5)
 
SELECT @OrderNo = [order_no]from inserted with (NOLOCK)
SELECT @OrderLineNo = [order_line_no]from inserted with (NOLOCK)
SELECT @Product = [product] from scheme.opdetm with (NOLOCK)where [order_no]=@OrderNo and [order_line_no]=@OrderLineNo
SELECT @Cost = [cost]from inserted with (NOLOCK)
SELECT @sql = 
'SELECT ' 
+ cast(i.order_no as varchar(100)) + ' OrderNo ,' 
+ cast(i.order_line_no as varchar(100)) + ' OrderLineNo ,'
+ '''' + replace(d.product , '''', '''''') + ''' product '
from inserted i with (NOLOCK)
JOIN scheme.opdetm d with (NOLOCK)
ON i.order_no = d.order_no
AND i.order_line_no = d.order_line_no
 
IF @Cost=0 and @Product <> 'CAT001' and @Product <> 'CAT002'
 
BEGIN 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'subject',
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
 
END

Open in new window

rickchild

DECLARE @Subject varchar(1000)

SET @SubjectTemp = 'Email Alert for Order No: ' +@OrderNo+ ' , Product: ' +@Product

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = @SubjectTemp,
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'

rickchild

Sorry:

DECLARE @SubjectTemp varchar(1000)

SET @SubjectTemp = 'Email Alert for Order No: ' +@OrderNo+ ' , Product: ' +@Product

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = @SubjectTemp,
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'

ASKER CERTIFIED SOLUTION

rickchild

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chapmandew

You do realize that if there are >1 rows inserted, then your trigger is only going to capture the last value into your variables, right?

meteorelec

ASKER

chapmandew,
Yes thats true how do i get around that?

Also
SET @SubjectTemp = 'Email Alert for Order No: ' +@OrderNo+ ' , Product: ' +@Product

it works but the problem is, is that i'm gettinglots of spacing after the order no

i.e.
Email Alert for Order No: 999993 , Product: CAT001

How would i trim that spacing away?

chapmandew

to trim:

lrtrim(rtrim(fieldname))

to get around the other values, you'll have to use a cursor:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2739031&SiteID=17

meteorelec

ASKER

I tried this but it wasn't working

SELECT @SubjectTemp = 'Email Alert for Order No: ' +LTRIM(RTRIM(@OrderNo)+ ' , Product: ' +@Product

chapmandew

I would focus on getting the cursor right....

meteorelec

ASKER

No i won't require a cursor for this particlar piece of code, as there will only be single inserts.

chapmandew

OK.

change this

DECLARE @OrderNo CHAR(10)

to this

DECLARE @OrderNo VARCHAR(10)

and try it again.

meteorelec

ASKER

Thanks a million,

chapmandew

welcome.

chapmandew

whoa...no points for the help?