June 14, 2011 at 11:27 pm
Jeff Moden (6/14/2011)
It's also going to have to be dynamic (I believe) which is going to make it even uglier and slower.
I think the idea would be a huge if else ladder based on the table name parameter, and then using different forms of openxml inside the block. So no dynamic SQL.
Uh... woo?
Send it back to hell!
June 15, 2011 at 3:06 am
is it advisable one procedure for each reference master's Insert,update and delete operations
June 16, 2011 at 2:48 am
To throw in my two cents worth (or should that be pence - I'm British)...
Lowell was spot on when he/she said "it depends".
In general I would recommend separate sprocs for Insert/Update/Delete. I have combined Insert/Update before, but this was in an extremely large system and I would have ended up with 1000 sprocs instead of 500!
Delete should always be separate, otherwise you run the risk of passing a parameter incorrectly to specify an update and you end up deleting the record. A programming function/procedure/macro or whatever you want to call it should have only one purpose.
Everybody else has shot down in flames what your manager is suggesting and I'm no different. From the basic premise presented, your manager sounds typical of many "IT" managers I have met, i.e. he knows only one way to do things and there is no way in hell he will ever deviate from it.
Good luck!
Mike.
June 16, 2011 at 5:20 am
hi
Thanks for your reply. Since our system is going to be a large system. I am going to keep insert/update in one procedure and delete in separate procedure. I sent the link to my manager. lets see what he replies. Thanks a lot for all of your support and suggestion.
June 16, 2011 at 7:27 am
G-d it pains me to say this but I actually agree with Joe Celko. This is an absolutely, ridiculously bad idea!!!
With code generation tools you can have standard CRUD sprocs generated in about 5 minutes.
I will applaud your manager for trying to think outside the box (not many do that), but in this case he went to a VERY bad place and needs to come back to reality. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 16, 2011 at 11:44 am
While your manager is outside the box ask him/her to consider what will happen when two people need to modify "The Proc"...one to change how Lady GaGa does her work and the other has to change what the automobile does?? Hmmm :Whistling:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 12:36 pm
Assuming your the developer and wouldn't have access to the live system , i don't know any dba who would execute this proc on the developers behalf with the xml data and no real control.
Frankly if it were me , i would insist on individual scripts for each table , this is master / reference data we are taking about , mistakes could bring down the system.just for plain and simple roll back purposes i would insist i have one commit and one rollback script , not to mention issues with identity inserts , referential integrity and small clean up in case i get errors.
overall , the so would need to be bullet proof before any dba could use it and thats a big commitment which your manager should be willing to stick to.
June 16, 2011 at 7:25 pm
opc.three (6/16/2011)
While your manager is outside the box ask him/her to consider what will happen when two people need to modify "The Proc"...one to change how Lady GaGa does her work and the other has to change what the automobile does?? Hmmm :Whistling:
Aw dude! Not a "celko-ism". :sick: 😉
--Jeff Moden
June 16, 2011 at 8:11 pm
Jeff Moden (6/16/2011)
opc.three (6/16/2011)
While your manager is outside the box ask him/her to consider what will happen when two people need to modify "The Proc"...one to change how Lady GaGa does her work and the other has to change what the automobile does?? Hmmm :Whistling:Aw dude! Not a "celko-ism". :sick: 😉
What? C'mon, that one was clever 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 10:04 pm
hi
Who is lady gaga?
June 17, 2011 at 6:49 am
vmssanthosh (6/16/2011)
hiWho is lady gaga?
*reach hand up, push rock away*
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 17, 2011 at 6:54 am
vmssanthosh (6/16/2011)
hiWho is lady gaga?
This is an American cultural reference to a sometimes strange pop singer. Search the web for any number of VERY interesting pictures of outfits she has worn.
No need for the "climb out from under the rock" statement. We need to remember that this is an international forum guys and gals! I know if someone made a comment about an Indian, Israeli, South African, etc pop singer or actor's odd behavior I would have no idea what the reference was about. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2011 at 7:27 am
TheSQLGuru (6/17/2011)
vmssanthosh (6/16/2011)
hiWho is lady gaga?
This is an American cultural reference to a sometimes strange pop singer. Search the web for any number of VERY interesting pictures of outfits she has worn.
No need for the "climb out from under the rock" statement. We need to remember that this is an international forum guys and gals! I know if someone made a comment about an Indian, Israeli, South African, etc pop singer or actor's odd behavior I would have no idea what the reference was about. 😎
You're absolutely right Kevin. :blush:
My apologies to you vmssanthosh. Have a look, she has quite an interesting public persona.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply