SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cursor concatenating variable


cursor concatenating variable

Author
Message
spin
spin
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 537
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


Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12858 Visits: 5478
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
spin
spin
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 537
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97287 Visits: 38988
Need to see the code that builds this table: dbo.tbl_STOCK_safety_check

Cool
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64251 Visits: 17974
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.

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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64251 Visits: 17974
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 Modens 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)
spin
spin
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 537
that's cracked it. thanks for the help.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97287 Visits: 38988
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!

Cool
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64251 Visits: 17974
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. :-P

_______________________________________________________________

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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search