One procedure to insertupdate all master tables using if statements
-
hi
our manager is giving a new idea of having only one procedure for insert update and deleting all the master tables.
he wants to specify the table name and the values in XML.
Please advice
-
hi
somebody please advice on this.. as per me i am saying its wrong.
i said it gives problem while deciding/providing execute permission for the database users.
but he says that we are giving in the table level.
Also i said while doing changes for one master. we need to touch the procedure which involves logic for creating all master. so its dangerous.
I need some value points or comments on this approach.
Waiting for the reply.
regards
VMSSanthosh
-
I'm afraid the answer is "it depends"...we'd need to really see the procedure to give you a peer review and tell you what we thing is good, bad, or could benefit from improvement. From your general statement about having a master proc or not, we can't really offer a concrete opinion one way or the other.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
My gut reaction is that this is an ugly solution (particularly bringing XML in, ugh... XML...).
Does your manager have a particular reason for wanting to do it this way?
One objection (from a design theory point of view) is that the XML data must be coming in "untyped". That is to say, since each table defines a type (by way of it's attribute set), and since it would only be by chance that the types for different tables would be identical, the XML must be able to be any of multiple types. Sure, you *can* get around strong typing by using XML this way, but I don't think you should.
-
hi
Actually we are in a design phase of a new project. And i am responsible for designing the databases.
We have not yet written any procedures yet ( we not even started designing). Meanwhile my manager is asking me to do so. I said its not a good idea. But i didnt get much points to speak to him. he wants to have one procedure for all reference masters insert and update operations.
the application which we are going to design will have many enhancements in the future.
I dont want to mess it up in the beginning of the project itself.
But i need strong reasons to oppose that idea. And if that idea is good, i am ready to follow.
But my sense is telling that is not a good idea.
I am helpless bcoz i am not getting strong reasons to oppose this.
Regards
VMSSanthosh
-
In my opinion it's not a good idea (for reasons of permissions, datatyping, and maintenance) unless your manager has some specific argument for wanting to do it in your situation. I can't imagine what the content of that argument might be, but it's possible.
If his argument is that it would be easier to maintain one stored procedure instead of many, I would disagree. Just come up with a naming convention for this kind of procedure and use it across all of your "master" tables. For instance, <tablename>_create, <tablename>_update, <tablename>_delete, etc.
Having all the code in one procedure means a really, really long procedure with a bunch of conditional logic executing different tasks. Standard good practise in programming is that one routine does one specific task.
-
The other thing that will ALWAYS come into play somewhere along the line. Initially it sounds like a generic procedure to handle all the dml for lookup tables can be generic enough but you will end up with several "one offs". Where you just add a minor tweak for just "this one" table. Suddenly your original 80-100 line sproc for generic master table maintenance is well over 1,000 lines and takes hours to find the specific logic for the new little addition to "this other" table. In effect you will end up with all the individual sproc logic inside a single sproc with a spiderweb of case and if conditions that make you want to pop your eyeballs. Just my 2¢.
Sounds like you are on the right path, when your gut just says "this is not the right way to do this" you step back and question it. That is how we all grow and learn from our own (or others) mistakes. Stick to your guns. Most likely if it just seems wrong it probably is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Agreed, that's an excellent example to demonstrate the reasoning behind "one routine for one task".
-
One Procedure To Rule Them All? Lord Of The Rings Style?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
vmssanthosh
hiour manager is giving a new idea of having only one procedure for insert update and deleting all the master tables.
he wants to specify the table name and the values in XML.
Please advice
This is so wrong in a production enviroment! I would not allow any users to execute anything on the masterdb(system database). This is a problem waiting to happen for you to have to fix!
I think this would be the only queries you should let them execute.
Select 'NO' on insert master
Select 'NO' on update master
Select 'NO' on delete master
"There are no problems! Only solutions that have yet to be discovered!" 😎
:cool:
"There are no problems! Only solutions that have yet to be discovered!" -
Lowell (6/14/2011)
One Procedure To Rule Them All? Lord Of The Rings Style?+1000
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
bopeavy (6/14/2011)
vmssanthosh
hiour manager is giving a new idea of having only one procedure for insert update and deleting all the master tables.
he wants to specify the table name and the values in XML.
Please advice
This is so wrong in a production enviroment! I would not allow any users to execute anything on the masterdb(system database). This is a problem waiting to happen for you to have to fix!
I think this would be the only queries you should let them execute.
Select 'NO' on insert master
Select 'NO' on update master
Select 'NO' on delete master
"There are no problems! Only solutions that have yet to be discovered!" 😎
I don't think the OP is talking tables in the master db but "master" or lookup tables for their system. Regardless I think we have all flogged the horse and it is now dead. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Lowell (6/14/2011)
One Procedure To Rule Them All? Lord Of The Rings Style?(img cut for brevity)
<Standing ovation/>
Saved.
-
bopeavy (6/14/2011)
--------------------------------------------------------------------------------
vmssanthosh
--------------------------------------------------------------------------------
hi
our manager is giving a new idea of having only one procedure for insert update and deleting all the master tables.
he wants to specify the table name and the values in XML.
Please advice
This is so wrong in a production enviroment! I would not allow any users to execute anything on the masterdb(system database). This is a problem waiting to happen for you to have to fix!
I think this would be the only queries you should let them execute.
Select 'NO' on insert master
Select 'NO' on update master
Select 'NO' on delete master
"There are no problems! Only solutions that have yet to be discovered!"
I don't think the OP is talking tables in the master db but "master" or lookup tables for their system. Regardless I think we have all flogged the horse and it is now dead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://qa.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Amen on folgging the horse!:hehe:
:cool:
"There are no problems! Only solutions that have yet to be discovered!" -
allmhuran (6/13/2011)
My gut reaction is that this is an ugly solution (particularly bringing XML in, ugh... XML...).Does your manager have a particular reason for wanting to do it this way?
One objection (from a design theory point of view) is that the XML data must be coming in "untyped". That is to say, since each table defines a type (by way of it's attribute set), and since it would only be by chance that the types for different tables would be identical, the XML must be able to be any of multiple types. Sure, you *can* get around strong typing by using XML this way, but I don't think you should.
I'll add to that... it's not only ugly, but it's going to be slow and it's going to clog the pipe because it takes a whole lot more bytes to transmit the same information as a "C.R.U.D." procedure. It's also going to have to be dynamic (I believe) which is going to make it even uglier and slower.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply