| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-22 : 08:06:54
|
| Anil writes "Hello Ppl -I need your advice on something that i'm trying to do here...My client has around 600 tables with different table schema's which we acreated recently...They want to have single generic stored procedure which will update the tables....Few things here..--> I'm planning to get the fields updated from UI through an XML Stream and parse the XML stream and prepare the UPDATE Statement using D-SQL....--> Since, all the tables have different table schema's i thought D-SQL is the only way through which we can prepare a particular update statement...My client is asking if we can have any metadata based approach to this...I'm kind of new to Metadata and with the knowledge that i have, i think we cannot go for a Metadata based approach since none of the tables are similar to each other...AM I RIGHT?--> Is D-SQL only way of approaching this? Or is there any other smart way of accomplishing this task of designing one single SP for 600 tables??Please advice me on this guys..Appreciate your help!" |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-22 : 08:16:15
|
quote:
They want to have single generic stored procedure which will update the tables
Back up a sec update how? You say the tables are not similar, do you put water in your car and drink Gas?JimUsers <> Logic |
 |
|
|
safecoder@gmail.com
Starting Member
22 Posts |
Posted - 2005-04-22 : 09:18:51
|
| Note: Just to make it clear...This stored procedure will update only single table per Stored procedure call...Well..I dont think it is impossible...yeah...I understand that it will hit the performance because we have to use the D-SQL...but i guess it is possible to create a stored procedure which will dynamically create an UPDATE statement using D-SQL depending on the Table structure and new values that need to be updated....and execute that "@SQL" String....but, the question is...Is D-SQL the only way for doing this ..or is there any other smart way...Thanks for your reply though Jim.. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-22 : 10:06:43
|
It is hard to understand why your client wants this. This doesn't sound like a business requirement. It sounds like someone who didn't know much about development read a magazine article that said "You should have a meta-data driven data layer".In most applications the requiremnts for maintaining data are fairly complex, so I cannot imagine that you will have much success with the approach of having one stored proc that does it all.You could point out that SQL Server stored procedures are meta data driven themselves. The prosedure names are stored in sysobjects, the parameters are stored in syscolumns, and the processing rules (SQL Code) are stored in syscomments.Why re-invent the wheel? quote:
...They want to have single generic stored procedure which will update the tables......My client is asking if we can have any metadata based approach to this...
CODO ERGO SUM |
|
|
|
safecoder@gmail.com
Starting Member
22 Posts |
Posted - 2005-04-22 : 10:32:26
|
| If we have a seperate Stored procedure for each table doing, then we are looking at around 600 stored procedures....And my manager is not very happy with it..And he wants to make a stored procedure completely transparent to the data tables....I liked the idea, but am not sure how am i going to implement it... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-22 : 10:54:11
|
I guees I would have to say that if you have a database with 600 tables, then you have a big, complex application.It sounds like your manager's concern is that he doesn't want to have to develop, test, and maintain that many procedures. I don't blame him, but so what? Just because he doesn't like it doesn't mean there is a better, cheaper way.Can you have a database without stored procedures? Yes, Peoplesoft has around 15,000 tables and 7,000 views without any stored procedures. That doesn't mean it was any easier to develop, just that all the SQL is in the application.Can the approach you are describing can be done with less work than developing stored procedures? I really doubt that it will be any cheaper or easier.You should look at programming approaches that make it easier to generate stored procedures. There is a lot you can do with SQL scripting to generate stored procedures. Develop a script that pulls the table and column names from INFOMATION_SCHEMA views, and generates a stored procedure with input parameters, output parameters, insert, update, or delete statements, begin and end transactions, and error handling. At the very least, this will greatly increase your productivity, and just leave the work of the custom logic for your application.quote:
Originally posted by safecoder@gmail.com
If we have a seperate Stored procedure for each table doing, then we are looking at around 600 stored procedures....And my manager is not very happy with it..And he wants to make a stored procedure completely transparent to the data tables....I liked the idea, but am not sure how am i going to implement it...
CODO ERGO SUM |
|
|
|
safecoder@gmail.com
Starting Member
22 Posts |
Posted - 2005-04-22 : 11:21:20
|
| Mike...Yes, i completely agree with you...Even i suggested my Lead about the SQL Script which can generate stored procedures for each table based on the table schema...but, he is more into one SP for all tables...:D..At the end of the day i should do what my Boss wants me to do..Why i like the idea of one SP is that, all the tables here are independent tables...No table is related to any other table and there can be only one Row that can be updated at once for one table....I have to talk to my DBA and see what he says...I guess he will SHOOT ME right away...;)... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-04-22 : 14:01:19
|
I think the stored procedure that you need already exists; it's called sp_executesql. Just make a wrapper procedure around it, and it does everything you need. Just store your "metadata" (procedure code) in a table, and you have everything you need. Of course, the work of creating that "metadata" will be about the same as creating all those procedures, but if it keeps the boss happy...quote:
Originally posted by safecoder@gmail.com
Mike...Yes, i completely agree with you...Even i suggested my Lead about the SQL Script which can generate stored procedures for each table based on the table schema...but, he is more into one SP for all tables...:D..At the end of the day i should do what my Boss wants me to do..Why i like the idea of one SP is that, all the tables here are independent tables...No table is related to any other table and there can be only one Row that can be updated at once for one table....I have to talk to my DBA and see what he says...I guess he will SHOOT ME right away...;)...
CODO ERGO SUM |
|
|
|
|
Writing one Stored proc for multiple tables