question

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