question

siera_gld avatar image
siera_gld asked

Looping through records to build values for Variable

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 ![alt text][1]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 = ' '+@EM_HEADER+' '+@EM_DETAIL+' ' [1]: /upfiles/ssc.bmp
dbmailvariablelooping
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.