One Stored Procedure

[nushahi]

what about create one stored procedure handle all select and insert and delete in my database table and take the name of the table and the id of the process that i want to do it (insert - update - delete ) and this stored procedure detect the fields of the table and the primary key .

instead of creating stored procedures to insert , update and delete to every table



if i make this stored procdure it will affect on performance or not ????

incase of i want to deal with any table i will ask him about fields ,primary key ,foreign key and so on

after that i will make my process of (update-select - delete)

 

[AlexCuse]

You don't really want one stored procedure to do everything. It will make your life a living hell when it's time to make a change.

Also, it sounds like you're using SQL Server so maybe you want this forum? forum183

Good Luck,

Alex

[small]----signature below----[/small]
According to recent reports, the US Missile Defense System is actually just Cole Hamels on Speed Dial.

Ignorance of certain subjects is a great part of wisdom

 

[jrbarnett]

Can a stored procedure detect fields in a table? yes. This is SQL server but others will be similar.

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'Region' --Put your table name here
ORDER BY ordinal_position

John

 

[SQLSister]

One stored proc to do all this is an extremelly poor idea. It might take less time to create, but it will cause problems in maintaining and it will be most inefficient in running. It will also be a security hazard (read about SQL Injection attacks to see why dynamic SQL is a Bad bad idea). Oh yes testing it will be a bad thing as well.

If you do this, I hope you have audit tables so that you can recover from the data integrity problems you will create.

"NOTHING is more important in a database than integrity." ESquared

 

[damber]

... even better.. you could just have 1 table - with thingname, thingvalue, relatedthing and relationtype columns - wouldn't that make things much easier on the table design, eh ?

you could also have a generic screen which had 1 column of things and the second column of thing values - then have an action button at the end that simply said 'do it'.

Actually, no, I've a better idea - why not have 1 column, and 1 row called 'data', then you could simply have 1 screen with one field and one button.. !!!!!!111!!!11!!1!1!1!! cool.... oh, wait... isn't that notepad ?

;-)

</removestonguefromcheekhopingpointismade>



A smile is worth a thousand kind words. So smile, it's easy!

 

[SQLDenis]

>>you could just have 1 table - with thingname, thingvalue

No, you would create one table called TheCatInTheHat with 2 columns called ThingOne and ThingTwo

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[elsenorjose]

@damber
@SQLDenis

No, no...

One ring to rule them all...