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 |
|
Bhavan
Starting Member
8 Posts |
 Posted - 2008-03-26 : 11:32:56
|
| Friends, Can you share some of your thoghts here please ?I've a View with the data as follows.basetable id status====================================================tab1_history 1810252227 3tab1 1820254907 3tab1 1820254878 1tab1 1820254937 3Now, the task is : writing a stored proc accessing this view and "updating the underlying table.status" for the matching IDs (ids passed at the cmdline)ex: in above 3 column view, we have a history table (tab1_history) & active table (tab1). Let's say if someone passed all those 4 ids as cmdline perameters to stored proc, then - the status update (say from 3 to 99) for the 1st ID should be done in the tab1_history table and rest 3 ids status update (say from whatever to 99) should be done in the tab1 table.Also anyway we can restrict the cmdline IDs limiting to 10 ?appreciate your experties thoughts w/ this task.Cheers |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 11:55:32
|
| You should do some searhing for Dynamic Sql. I think that is the only method that will meet your requirements. |
 |
|
|
QAZAFI
Yak Posting Veteran
50 Posts |
Posted - 2008-03-26 : 15:46:44
|
| How Many tables you got in this view If you please tell me then It will be far easy to answer that Question |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-03-26 : 16:06:59
|
| This looks like a potential future nightmare, please explain in more detail what you are trying to accomplish, and maybe the solution here is to modify your plan of execution rather then building a solution around the process that you have stated. |
|
|
|
Bhavan
Starting Member
8 Posts |
Posted - 2008-03-27 : 06:35:09
|
| OK, let me answer both of your questions here.for QAZAFI - as it stands now, the view just has two tables (one is active, another one is History).for Vinnie881 - The situation is, application infrastructure is only limitied to read the active table and do the further processesing whatever it has to do, based on the TABLE.STATUS (say, if status=0, do xyz, if status=2 do nonthing etc..). However, at times there are requirements to re-processes the IDs whic have been moved from active table to history table (and attimes even from the active table, as I said based on the status). So, to get them processesed by the application infrastructure, I need to read the IDs through the view and take a decession.i.e if basetable = active table, then update the status to 99 (application infrastructure knows what it has to do if the status=99)if basetable = history table, then insert the ID back into active table and update the status to 99. so that historical ID will get processed again.The Only Thing is , we would never know how mnay number of ID s that required to be re-processed again and again. So, trying to limit the IDs to 50 at a time, as it's highly impossible to re-porcesses the thousands/millions of IDs at a time.Hope this explains better what I was trying to accomplish here.Cheers |
|
|
|
|
|
|
|
|
Help Pls !