Post reply

cursor concatenating variable

  • March 8, 2013 at 4:44 am

    #275087

    hi all

    i'm writing a cursor to send emails when our stock level goes below a safety value. The cursor seems to work fine except i have a in my email subject i use a @sku variable which at the moment is concatenating all the sku's for each mail.

    i.e.

    the first email is fine: subject - SKU0001 IS BELOW SAFTEY

    the second email reads : subject - SKU00014SKU0002 BELOW SAFETY

    the third will read : subject - SKU00014SKU0002SKU0003 BELOW SAFETY

    here's the code:

    DECLARE

    @emailTo VARCHAR(255),

    @email VARCHAR(max),

    @sku VARCHAR(30),

    @emailSubject VARCHAR(128),

    SET @emailTo = 'myemail@mycompany.co.uk'

    SET @emailSubject = 'Warning stock is below safety for : '

    -- SELECT RECORDS THAT NEED TO BE EMAILED

    DECLARE c1 CURSOR FOR

    SELECT @emailTo as email_add,

    rtrim(StockCode) as StockCode

    FROMdbo.tbl_STOCK_safety_check

    WHEREemail_status = 1

    -- LOOP THROUGH RECORDSET AND SEND EMAIL FOR EACH RECORD

    OPEN c1

    FETCH NEXT FROM c1 INTO @email, @sku

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    SELECT

    @emailSubject = @emailSubject + @sku

    EXEC msdb.dbo.sp_send_dbmail

    @importance = 'High',

    @recipients= @email ,

    @subject = @emailSubject

    FETCH NEXT FROM c1 INTO @email,@sku

    END

    CLOSE c1

    DEALLOCATE c1

  • March 8, 2013 at 8:32 am

    #1594991

    And what is the problem?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 8, 2013 at 8:50 am

    #1594999

    the problem is that the email subject should be this...

    email 1 subject : stockcode SKU0001 is below safety

    email 2 subject : stockcode SKU0002 is below safety

    email 3 subject : stockcode SKU0003 is below safety

    ..not this...

    email 1 subject : stockcode SKU0001 is below safety

    email 2 subject : stockcode SKU0001SKU0002 is below safety

    email 3 subject : stockcode SKU0001SKU0002SKU0003 is below safety

    which is whats happening.

  • March 8, 2013 at 9:29 am

    #1595028

    Need to see the code that builds this table: dbo.tbl_STOCK_safety_check

  • March 8, 2013 at 9:50 am

    #1595043

    That is because you concatenate values to the previous subject each pass through the loop.

    Change you loop to this.

    --@emailSubject = @emailSubject + @sku

    EXEC msdb.dbo.sp_send_dbmail

    @importance = 'High',

    @recipients= @email ,

    @subject = @emailSubject + @sku

    The subject portion stays the same 'Warning stock is below safety for : ', all you need is to add the sku to this.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 8, 2013 at 9:58 am

    #1595048

    You don't need to have email as a constant in your select for your cursor, just use the value that is already there.

    The bigger question I would ask is do you really need a separate email for each sku or would a single email with all the skus be acceptable? If a single email with multiple skus would be ok you don't even need the cursor. And do you mean for these emails to be only a subject and not have a body?

    --EDIT--

    Meant to post the cleaned up version of your cursor.

    DECLARE @emailTo VARCHAR(255),

    @email VARCHAR(max),

    @sku VARCHAR(30),

    @emailSubject VARCHAR(128),

    SET @emailTo = 'myemail@mycompany.co.uk'

    SET @emailSubject = 'Warning stock is below safety for : '

    -- SELECT RECORDS THAT NEED TO BE EMAILED

    DECLARE c1 CURSOR FOR

    SELECT rtrim(StockCode) as StockCode

    FROMdbo.tbl_STOCK_safety_check

    WHEREemail_status = 1

    -- LOOP THROUGH RECORDSET AND SEND EMAIL FOR EACH RECORD

    OPEN c1

    FETCH NEXT FROM c1 INTO @sku

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail

    @importance = 'High',

    @recipients= @email ,

    @subject = @emailSubject + @sku

    FETCH NEXT FROM c1 INTO @email,@sku

    END

    CLOSE c1

    DEALLOCATE c1

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 8, 2013 at 9:59 am

    #1595049

    that's cracked it. thanks for the help.

  • March 8, 2013 at 10:02 am

    #1595050

    Sean Lange (3/8/2013)

    That is because you concatenate values to the previous subject each pass through the loop.

    Change you loop to this.

    --@emailSubject = @emailSubject + @sku

    EXEC msdb.dbo.sp_send_dbmail

    @importance = 'High',

    @recipients= @email ,

    @subject = @emailSubject + @sku

    The subject portion stays the same 'Warning stock is below safety for : ', all you need is to add the sku to this.

    How the heck did I miss that!

  • March 8, 2013 at 10:08 am

    #1595056

    Lynn Pettis (3/8/2013)

    How the heck did I miss that!

    You saw a cursor and your eyes rolled back in your head...then you realized that this really might be one of those rare situations where a cursor actually does make sense...but by then you were so frazzled at trying to help somebody unravel a cursor you couldn't see straight anymore. I did feel a large disturbance about that time. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy