| Author |
Topic |
|
praveenanand
Starting Member
2 Posts |
 Posted - 2007-10-09 : 08:13:47
|
| 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 & regardssuman |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 08:14:57
|
| only with the use of dynamic sql_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-09 : 08:15:09
|
| It's not good idea even if it is possible, Period!Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 08:22:48
|
| you know something... i'm having serious second thoughts if this is not a good idea..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-09 : 08:26:58
|
| Why?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 08:39:09
|
| well... having 1 sproc for simple update/delete/insert is easier on the maintainance side (with proper logging)security of dynmic sql isn't an issue that much in ss2k5...assuming you won't be using triggers i don't really see the benefit of N sprocs anymore..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-09 : 08:45:50
|
| I think maintainance is going to be much painful as the system is going to expand. Lets say if he needs to add a special processing or validation for sinlge table insert, then its going to difficult to fit it in the generic SP. Sooner or later, the SP is going to be filled with lot of such special conditions and then it will grow out of control.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 08:46:46
|
exactly. that's why i said "simple"  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-09 : 08:49:18
|
| But frankly, I haven't seen any SP which remains simple over a period of time! Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 09:30:42
|
| "having 1 sproc for simple update/delete/insert is easier on the maintainance side"How would the parameters (one per column I presume?) work?Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 09:46:20
|
| well if you do dynamic... go dynmaic all the way _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 09:52:36
|
Ah, so all your CRUD sprocs are now sp_ExecuteSQL eh?Yup, I can see that that would work ...  |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-09 : 09:56:34
|
| roftlol... yeah.. it's a bad idea... my bad_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-09 : 10:09:40
|
create proc OnlyProcYouWillEverNeedUntilYouGetFired(@sql varchar(8000)) as exec(@sql)
elsasoft.org |
|
|
|
|
Generic insert and update procedure