Looping through table
-
I am simply trying to look through a table select out email(recipient), and combing to columns into one(body and unique) and can't seem to do it.My query will generate 4 emails, but all four contain data from the first row of table. What am i missing?
Here is table:
CREATE TABLE [dbo].[TestData](
[text] NULL,
[LTNM ] [varchar](100) NULL,
[varchar](100) NULL,
[CREATEDBY] [varchar](100) NULL,
[BODY] [varchar](800) NULL,
[UNIQUE] [varchar](40) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
data:
EMAILLTNMREC_EMAILCREATEDBYBODYUNIQUE
test1l@blah.comtest1,one1testl@blah.combajackson12345609329952586HVWBRDWMHPKD
test2@blah.comtest2,one2test@blah.combajackson1234113299503766ZSGCEECCBHQB
test3l@blah.comtest3,one3test@blah.comatholder123516373455442CXVEDSOHUTID
test4@blah.comtest4,one4test@blah.comjppompa12973199046577MNMEEUEIICWR
DECLARE @count int
set @count = 1
DECLARE @Recepient_Email VARCHAR(MAX)
DECLARE @Body1 VARCHAR(MAX)
while (@count <=(select COUNT(*) from TestData))
begin
set @Recepient_Email = (select top(1) EMAIL From TESTDATA where @count=@count)
set @Body1 = (select top(1) TESTDATA.BODY + '' + TESTDATA.UNIQUE from TESTDATA where @count=@count)
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA',
@recipients=@Recepient_Email,
@subject = 'This is subject of test Email',
@body =@Body1,
@body_format = 'HTML'
set @count =@count +1
END
-
it's because you aren't actually looping through the data. put an identity column in and then reference it with your count and it will likely work fine.
you might also want to pull the select count out of your loop and only call that once and set it to a variable.
@count=@count is the problem. should be identity_column_name=@count
-
Your problem is the select statements inside your loop.
set @Recepient_Email = (select top(1) EMAIL From TESTDATA where @count=@count)
set @Body1 = (select top(1) TESTDATA.BODY + '' + TESTDATA.UNIQUE from TESTDATA where @count=@count)
The values will always be the same (well they should be) because your where clause has nothing to do with the row. The reason I say should be is because you have no order by with your top 1.
If you do this in a loop you would probably be better off using a cursor. However, I don't think you need a loop here though. Can you just do an insert into a holding table and have a sql job pick up rows from that table and send out the email?
_______________________________________________________________
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/ -
richard.noordam (7/22/2013)
it's because you aren't actually looping through the data. put an identity column in and then reference it with your count and it will likely work fine.you might also want to pull the select count out of your loop and only call that once and set it to a variable.
@count=@count is the problem. should be identity_column_name=@count
If you use this type of approach I would recommend using ROW_NUMBER() instead of an identity. If you have gaps in your identity it could prove to be challenging. 😉
_______________________________________________________________
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/ -
Sean Lange (7/22/2013)
richard.noordam (7/22/2013)
it's because you aren't actually looping through the data. put an identity column in and then reference it with your count and it will likely work fine.you might also want to pull the select count out of your loop and only call that once and set it to a variable.
@count=@count is the problem. should be identity_column_name=@count
If you use this type of approach I would recommend using ROW_NUMBER() instead of an identity. If you have gaps in your identity it could prove to be challenging. 😉
agreed, if you are loading the table then using it, the identity column works great. load then use... if not then ROW_NUMBER() is better.
-
Done and Done, and Thanks
-
TryingToLearn (7/22/2013)
Done and Done, and ThanksDid you fix the loop or fix the code so it doesn't need a loop?
_______________________________________________________________
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/ -
as a first step i fixed the loop, used variable for count. I tried before(and failed) and will try again to use row_number, and will also try the load into table, but won't the email program need to loop through same table to send emails? Now that i have a fix, i will try to use each suggestion and use the best one. Thanks...I do appreciate the free training...
-
TryingToLearn (7/22/2013)
as a first step i fixed the loop, used variable for count. I tried before(and failed) and will try again to use row_number, and will also try the load into table, but won't the email program need to loop through same table to send emails? Now that i have a fix, i will try to use each suggestion and use the best one. Thanks...I do appreciate the free training...Yes somewhere along the line you will have to pick them up one at a time. It sort of depends on if you have people waiting for this to run. If you load it to a holding table, it will be able return to the user a bit quicker.
_______________________________________________________________
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/ -
This is why cursors exist. Just use a cursor, unless you have an extraordinarily high volume of emails to send.
CREATE TABLE [dbo].[TestData](
[text] NULL, --?? why not change to varchar(max) ??
...
DECLARE csr_email CURSOR FAST_FORWARD FOR
SELECT
CAST(email AS varchar(max)) AS email,
body,
unique
FROM [dbo].[TestData]
--WHERE ...
DECLARE @email varchar(max)
DECLARE @body varchar(8000)
DECLARE @unique varchar(400)
DECLARE @body1 varchar(8000)
OPEN csr_email
WHILE 1 = 1
BEGIN
FETCH NEXT FROM csr_email INTO @email, @body, @unique
IF @@FETCH_STATUS = -1
BREAK
IF @@FETCH_STATUS = -2
CONTINUE
SET @body1 = @body + '' + @unique
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',
@recipients = @email,
@subject = 'This is subject of test Email',
@body = @body1,
@body_format = 'HTML'
END --WHILE
DEALLOCATE csr_email
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply