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

LVL 2
meteorelecAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rickchildConnect With a Mentor Commented:
So to put that in your code
DECLARE @Cost FLOAT
DECLARE @sql VARCHAR(1000)
DECLARE @Product CHAR(20)
DECLARE @OrderNo CHAR(10)
DECLARE @OrderLineNo CHAR(5)
DECLARE @SubjectTemp varchar(1000)
 
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
 
SET @SubjectTemp = 'Email Alert for Order No: ' +@OrderNo+ ' , Product: ' +@Product
 
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 = @SubjectTemp,
@body = 'body',
@query = @sql,
@execute_query_database = 'demo'
 
END

Open in new window

0
 
rickchildCommented:
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'
0
 
rickchildCommented:
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'
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
chapmandewCommented:
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?
0
 
meteorelecAuthor Commented:
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?
0
 
chapmandewCommented:
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
0
 
meteorelecAuthor Commented:
I tried this but it wasn't working

SELECT @SubjectTemp = 'Email Alert for Order No: ' +LTRIM(RTRIM(@OrderNo)+ ' , Product: ' +@Product
0
 
chapmandewCommented:
I would focus on getting the cursor right....
0
 
meteorelecAuthor Commented:
No i won't require a cursor for this particlar piece of code, as there will only be single inserts.
0
 
chapmandewCommented:
OK.

change this

DECLARE @OrderNo CHAR(10)

to this

DECLARE @OrderNo VARCHAR(10)

and try it again.
0
 
meteorelecAuthor Commented:
Thanks a million,
0
 
chapmandewCommented:
welcome.
0
 
chapmandewCommented:
whoa...no points for the help?
0
All Courses

From novice to tech pro — start learning today.