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 |
|
MuadDBA
628 Posts |
 Posted - 2002-11-05 : 15:42:28
|
| I have a stored procedure called LOAN_UPDATE which (duh) updates loan records. My developer would like to updatemany records at once, but of course, my SP only allows one at a time.To get around this limitation, he is using cursors to page through the records he wishes to update, build the command for the SP, and execute it.I know there is a way for him to do it with dynamic SQL instead of cursors, but I can't wrap my brain around it. If anyone can help, I would be most appreciative. I tried this:declare @cmd varchar(8000)select @cmd = 'loan_update @loan = '+loan+', status = 500go' where status = 200But it truncates the statement after the first line...so where am I going wrong? Can someone help me? |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-05 : 15:52:03
|
| More info, please.What do you mean by truncates? Are you getting an error? Is the "loan" too long? Is it character data? Is it a variable? Where are you trying this, in your SP? Can this WHERE return more than one row? Why is there no FROM clause?Sarah Berger MCSD |
 |
|
|
MuadDBA
628 Posts |
Posted - 2002-11-05 : 16:07:14
|
| sorry, it should be FROM LOAN where ....etcYes, this should return more than one row.When I say it truncates the command, what I mean is if you view the value of te @cmd variable, you see one iteration of the LOAN_UPDATE statement in there, but nothing else...I am expecting hundreds of them |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-06 : 04:52:23
|
| If you want to call an SP which updates a single record then you are restricted to looping through the records and have a design problem.Dynamic sql won't help much.your command should be something likedeclare @cmd varchar(8000) select @cmd = ''select @cmd = @cmd + 'exec loan_update @loan = '+loan+', status = 500 ' where status = 200 exec (@cmd)Another way to do this would be to populate a temp table (or permanent one keyed on spid or identifier) and the called SP joins to that to do the update rather than doing one record at a time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 11/06/2002 04:54:01 |
|
|
|
|
|
|
|
|
Bad Bad Cursors