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 |
|
interclubs
Yak Posting Veteran
63 Posts |
 Posted - 2004-02-09 : 12:24:01
|
| I have a stored Proc. 'GetAddress' which takes an ID and return an address. I am trying to loop thru a table and update the information...like This:Update MailAddress set Address = GetAddress MailAddress.IDso essentially it would go thru the table and execute the 'GetAddress' stored proc passing the ID from MailAddress to update each row.Unfortunately though, I can't get it to work. I know something is wrong with my syntax but I am at a loss for trying to figure out what.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-09 : 12:25:52
|
| Well, what is your syntax?Tara |
 |
|
|
interclubs
Yak Posting Veteran
63 Posts |
Posted - 2004-02-09 : 12:38:47
|
| Literally is:Update MailAddress set MailAddress.Address = GetAddress MailAddress.IDwhere (MailAddress.Address is null) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-09 : 12:53:53
|
| Looping is not good in SQL Server. Will this be a one-time thing or will it be part of your application? If it needs to be part of your application, GetAddress stored proc should be changed to handle multiple rows.Tara |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-02-09 : 13:03:44
|
| If GetAddress was a function, rather than a proc, you could execute the update as you have it, otherwise you'll need a cursor.For the function:update MailAddress set Address = dbo.GetAddress(MailAddress.ID)JonathanGaming will never be the same |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-09 : 13:04:10
|
| Let me ask this...where are you getting the mail id's from in the first place?Brett8-) |
|
|
|
interclubs
Yak Posting Veteran
63 Posts |
Posted - 2004-02-09 : 13:27:18
|
| Thanks guys, I changed the stored proc and removed the looping. Seems to be working fine now. |
|
|
|
|
|
|
Looping thru table and running stored proc update