x

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 =  '<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>'
[1]: /upfiles/ssc.bmp
more ▼

asked Nov 18, 2011 at 09:49 AM in Default

siera_gld gravatar image

siera_gld
1k 79 84 85

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.
Nov 21, 2011 at 01:49 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x29
x28
x12

asked: Nov 18, 2011 at 09:49 AM

Seen: 1146 times

Last Updated: Nov 18, 2011 at 02:48 PM