| Author |
 Topic  |
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
 Posted - 04/23/2008 : 10:42:00
|
When populating a variable, does it retain its value for the next loop? It seems when outputting a variable's contents, these are compounded and keep adding a single row over and over. I can't see any logic which is making this happen. This should be fetching detail for each row, one by one and outputting seperately. It looks like each file is 100s of copies of itself.
Seen any of this before? I was thinking of emptying the variable at the end of each iteration. How do you do it?
SELECT @thisvar = ''
First instruction at the start of the iteration is:
SELECT @PDF = '<HTML> ........... @var1..., @var2, etc' |
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 04/23/2008 : 11:34:46
|
I think we're going to need more details. Perhaps an example? Better still, take a step back and explain the bigger picture - you may well not need a loop at all.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/23/2008 : 11:44:10
|
If you really need to do looping, which I suspect you don't, it might be better to do it in the application layer.
What exactly are you trying to accomplish? There is likely a much better way than trying to do looping in SQL. |
Edited by - cat_jesus on 04/23/2008 11:44:34 |
|
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
Posted - 04/23/2008 : 12:26:44
|
It has been complex. Now it's simple
Producing HTML riddled with variables out in emails. Looping so each goes to a different recipient with different details in each row. Perfect. Each batch can have 0, 1 or any number of rows in the temp table. All works a treat. Using xp_smtp_sendmail
Now I just have to copy the stored procedure and adjust it slightly for a new task. Same HTML with variables in it. But not sent as an email. Exported to individual .txt files to be converted from HTML code to a individual PDFs. Using sp_appendToFile
The loop is valid and what matters is that all that changes is sp_appendToFile from xp_smtp_sendmail.
But in the mailing there was a spool of emails. In this one, a spool of .txt. The good .txts are 6k. Some bad ones are 5Mb, repeating themselves thousands over and over... |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/23/2008 : 14:00:46
|
I wasn't saying that the loop was invalid but that it was likely the hard and inefficient way to do it. Of course without details we are only left to speculate using our past experience as a guide.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
| |
Topic |
|
Loops & Variables
