Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2008-04-23 : 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
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-23 : 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 - 2008-04-23 : 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. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-04-23 : 12:26:44
|
| It has been complex. Now it's simpleProducing 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_sendmailNow 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_appendToFileThe 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 - 2008-04-23 : 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
30421 Posts |
|
|
|
|
|
|
|
Loops & Variables