March 8, 2013 at 4:44 am
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
March 8, 2013 at 8:50 am
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
Need to see the code that builds this table: dbo.tbl_STOCK_safety_check
March 8, 2013 at 9:50 am
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
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
that's cracked it. thanks for the help.
March 8, 2013 at 10:02 am
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
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply