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 |
|
safecoder@gmail.com
Starting Member
22 Posts |
 Posted - 2005-04-20 : 11:46:56
|
| guys...need some advice...I know that writing a generic SP for updating is gonna hit performace(because of DSQL), but the client wants a generic SP ...Will out reqmt and my thoughtsRequirement-----------Client has around 600 independent tables with different table schema's (ranging from 2 column table to 25 column table..)..now, they want a "Metadata based Stored procedure" to update data in whatever table that we want...My thoughts-----------Since, the table schema's are not consistent, we cannot make or even think of Metadata based stored procedure...Is that right??? I was thinking of designing a SP which takes the table name and all the values that needs to be updated....Later, i will build a SQL string using D-SQL and execute the String to update the data...My questions---------------> Since, the values that we are gonna pass to SP gonna vary based on Tables, How can we define the Stored procedure? I mean, one table needs only 2 values to be updated...but other on might require 20 values to be updated...Is there any data type in SQL Server which can take all the values and stick in to an Array kind of thing?Like..create Stored procedure as Generi_CP asTablename varchar,values ArrayTypeGohow to deal with this?--> I think varchar has amaximum length of 8000 Chars...What is the DSQL ends up with a longer SQL String? how we are gonna execute that?--> Is there any other way of dealing with this whole Generic stored procedure concept without using D-SQL?Can we use Metadata by any chance? (I'm completely new to Metadata..So i dont exactly get the whole concept of Metadata)...please advice me on this...Thanks, |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-04-20 : 12:04:48
|
| "shoot the client"....best starter advice you're going to get."what the client want's and what's best for the client" in this case would seem like 2 ends of a piece of string....a very long string."I think varchar has amaximum length of 8000 Chars...What is the DSQL ends up with a longer SQL String? how we are gonna execute that?"...EXEC( @SQL1 + @SQL2 + @SQL3)Good luck with the hunt! |
 |
|
|
safecoder@gmail.com
Starting Member
22 Posts |
Posted - 2005-04-20 : 13:01:57
|
| loll...EXEC(@SQL1+SQL2+SQL3)...I did that before..Just wasn't in my mind..Anyway thanks for reminding me of that...I'm still researching on how to deal with the problem...Whsts best..how i can go for Metadata based SP... |
|
|
|
|
|
|
Generic Metadat based Stored Procedure 600 tables