October 9, 2007 at 5:56 am
hi all,
i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar... and let say table2 may have col1 varchar,col2 char..like that..and so on..
generally till now wat iam doing is that iam writting different stored procedure for different tables..
Now i want to write One Insert & update procedure in my project...and for all the tables in the project i want to use only that procedure.
Is it Possible? If yes kindly help me ..on resolving this issue..
Thanks & regards
suman
October 9, 2007 at 6:02 am
you'd have to use dynamic sql within your proc building your statements as you go and then executing the created script.
I'd say this would be the worst and most inefficient method of implementing an action in sql server and I'd seriously advise against it.
The number of permutations and such required to build this sort of thing inevitably lead to code which is difficult to maintain and will break often.
When you think about your question and how long SQL databases have been around don't you think if there was such a solution it would be built into the database engine?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 9, 2007 at 6:55 am
It is possible, but as Colin has noted, it's definitely not the right approach. If you were to try it, the trick would be to only use a single parameter, XML, and then assume that the XML is perfect, the data conversions are perfect, and build the insert queries & update queries on the fly using the dynamic management views to get the table definitions. It's going to be a gigantic amount of work. It'll be about as stable as a house of cards built on top of jello, but you could do it.
Instead, if you're trying to avoid having to write a hundred different simple insert/update statements, look at a tool like CodeSmith to generate the insert statements for you. You end up with less work, but an accurate series of stored procedures using correct data types,etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2007 at 7:52 am
While I agree with the two responses above, why do you want to do this? I'd be curious to know what you're trying to achieve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply