I am developing an email alert and it works for one item at a time - but when items of similar type meet the same requirements for the alert - they need to be included with the same email.
How to i loop through and build all of the items to be included with me email details as shown below
I am developing an email alert and it works for one item at a time - but when items of similar type meet the same requirements for the alert - they need to be included with the same email.
How to i loop through and build all of the items to be included with me email details as shown below
@EM_HEADER = (
SELECT DISTINCT 'Issue#: ' +ISSUE + CHAR(13)+CHAR(10) +
'Customer Account Name: ' + [CUSTOMER_NAME] + CHAR(13)+CHAR(10) +
'Original Submission Date:'+CAST(CONVERT(CHAR(10),[OPEN_DATE],101)AS VARCHAR) + CHAR(13)+CHAR(10) +
'Price Reduction Effective Date: ' +CAST(CONVERT(CHAR(10),[LAST_PRC_CHNG_DT],101)AS VARCHAR)
),
@EM_DETAIL =(
-- BEGIN
-- WHILE EXISTS (SELECT * FROM #DETAILS)
DECLARE @ITEM CHAR(10)
SELECT @ITEM = MIN(EM_ITEM_NUM) FROM #DETAILS
SELECT 'Item Sell Description: '+DESCRIPTION + CHAR(13)+CHAR(10) +
'Item #: '+ITEM_NBR + CHAR(13)+CHAR(10) +
'Price Reduction Percent:' +CONVERT(VARCHAR,CASE WHEN CHANGE_PCT < 0 THEN CHANGE_PCT*-100 ELSE CHANGE_PCT END)+'%'+
CHAR(13)+CHAR(10)+ CHAR(13)+CHAR(10)
FROM #DETAILS
WHERE ITEM_NBR IN (@item)
DELETE FROM #DETAILS WHERE ITEM_NBR IN (@item)
-- END
SET @BODY = '<html><body style=''font-family:Calibri;font-size:12pt;font-weight:normal;''>
<table style=''font-family:Calibri;font-size:12pt;font-weight:BOLD;''>
<tr>'+@EM_HEADER+'</tr>
<tr>'+@EM_DETAIL+'</tr>
</table>
</body>
</html>'
asked
Nov 18 '11 at 09:49 AM
in Default
siera_gld
936
●
52
●
70
●
74
it seems like this is not a production working code (must be tweaked). Please let us know the DDLs of the objects, some test data and the desired output to help you better.