Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

cursor concatenating variable Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 4:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
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
FROM dbo.tbl_STOCK_safety_check
WHERE email_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

Post #1428514
Posted Friday, March 8, 2013 8:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
And what is the problem?


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

How to post your question to get the best and quick help
Post #1428608
Posted Friday, March 8, 2013 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
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.
Post #1428617
Posted Friday, March 8, 2013 9:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:00 PM
Points: 22,992, Visits: 31,470
Need to see the code that builds this table: dbo.tbl_STOCK_safety_check



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428648
Posted Friday, March 8, 2013 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428665
Posted Friday, March 8, 2013 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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
FROM dbo.tbl_STOCK_safety_check
WHERE email_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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428670
Posted Friday, March 8, 2013 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:25 AM
Points: 116, Visits: 402
that's cracked it. thanks for the help.
Post #1428672
Posted Friday, March 8, 2013 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:00 PM
Points: 22,992, Visits: 31,470
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!



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428673
Posted Friday, March 8, 2013 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse