Single stored procedure for insert update delete,is it good or bad?

Question

Hi, Is it correct way for writing the stored procedure or not? please advice

Dipal Choksi · Answer

If you use a single SP for all the 3 operations, you will have to modify only one SP in case the db table changes. The overhead is that you will have to include a check for the operation in each call to the SP. Also, it is more prone to errors. So, it depends on your environment - if you are expecting the underlying data to change frequently, it may make more sense to use one SP.

Pravin More · Answer

but nethra, according to my sproc you are passing where condition in @whereclause,so you can give condition on ID field in @whereclause like below..... exec spname '','prodname='newname'','id=11','TableName',2 and incase of insert there is no need because it autogenerated... Thanks, Pravin.

Nethra R S · Answer

In my case i want to create a stored procedure for insert update delete and also i am autogenerating the id field while inserting data.. So i have not declared the id field parameter. But i need it for update. When i execute it gives a error that scalara variable not declared

Prabhu Raja · Answer

I suggest you to Create Separate Stored Procedures for Insert, Update and Delete. So, that you can easily update your Stored Procedures in future. Check out this Article : http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx T he Article explains how to Call and Pass Parameters to Stored Procedure from Code-Behind.

Pravin More · Answer

Hi Nethra, You can call it from codebehind by same logic i explained you to call sproc from sql editor.... still i am explaining codebehind here............... //In case of insert string valuecluase="11,'abc','ss'"; spcon.Open(); spcmd = new SqlCommand("spname", spcon); spcmd.CommandType = CommandType.StoredProcedure; spcmd.Parameters.Add(new SqlParameter("@valueClause", valuecluase)); spcmd.Parameters.Add(new SqlParameter("@SetClause", "")); spcmd.Parameters.Add(new SqlParameter("@whereClause","")); spcmd.Parameters.Add(new SqlParameter("@Table_name", "Student")); spcmd.Parameters.Add(new SqlParameter("@case", 1)); dr = spcmd.ExecuteReader(); //In case of Update string SetClause="prodname='newname'"; string whereClause="prodname='newname'"; spcon.Open(); spcmd = new SqlCommand("spname", spcon); spcmd.CommandType = CommandType.StoredProcedure; spcmd.Parameters.Add(new SqlParameter("@valueClause", "")); spcmd.Parameters.Add(new SqlParameter("@SetClause", SetClause)); spcmd.Parameters.Add(new SqlParameter("@whereClause",whereClause)); spcmd.Parameters.Add(new SqlParameter("@Table_name", "Student")); spcmd.Parameters.Add(new SqlParameter("@case", 2)); dr = spcmd.ExecuteReader(); //In case of delete string whereClause="prodid=11"; spcon.Open(); spcmd = new SqlCommand("spname", spcon); spcmd.CommandType = CommandType.StoredProcedure; spcmd.Parameters.Add(new SqlParameter("@valueClause", "")); spcmd.Parameters.Add(new SqlParameter("@SetClause", "")); spcmd.Parameters.Add(new SqlParameter("@whereClause",whereClause)); spcmd.Parameters.Add(new SqlParameter("@Table_name", "Student")); spcmd.Parameters.Add(new SqlParameter("@case", 3)); dr = spcmd.ExecuteReader(); Note:- Values passed for parameter will be changed according to your situation but calling logic will be same. Thanks, Pravin.

Nethra R S · Answer

@Pravin More, How will you call this from code behind? I mean how will you call the stored procedure from code behind

Pravin More · Answer

ya its upto person...........

Nethra R S · Answer

Hi Pravin, Its complex deciding when to do what

Pravin More · Answer

Hi Nethra, see,its depend on complexity of sproc,i will combine only that many functions in single sproc tha i am able to manage.......see for example i shown you their are 3 function n 5-6 params......if that params increased to 10 then i will go for another sproc....... Am i Right?

Nethra R S · Answer

Hi Pravin, If it is so, why are people writing so many stored procedures for so many different functions.. How will your method be more useful?

Pravin More · Answer

you missed thread nethra...it here.....lolz yes nethra.....its enough for 1 database as you are passing table names and colmn name as parameter dynamically...... and if you want it for more than database then pass table name as databasename..tablename thanks, Pravin.

Pravin More · Answer

Hi Nethra, here is description of how to call my that sproc.......... see these are the parameter... @valueClause varchar(max), @SetClause varchar(max), @whereClause varchar(max), @Table_name varchar(max), @case int -- will be 1 for insert,2 for update ,3 for Delete call for insert--------- exec spname '11,''abc'',''ss''','','','TableName',1 call for Update--------- exec spname '','prodname='newname'','prodid=11','TableName',2 call for delete--------- exec spname '','','prodid=11','TableName',3 Thank You, Pravin.

Nethra R S · Answer

Hi Pravin, Here in this link http://www.c-sharpcorner.com/Forums/Thread/139906/single-stored-procedure-for-updatedelete-and-insert.aspx you have created a stored proc is that a single stored proc for any table? and how will u call that procedure?

Suthish Nair · Answer

A good design must contain seperate SP for each functionality. For example.. Input parameters for Insert, Update and Delete will be always different. Instead of mixing up every thing, its better to have seperate Sp's. But we can also do insert, update, or delete operations in a single statement using the MERGE statement.

Pravin More · Answer

yes......i think its good ...i dont think it will create problem. i have used like that

Nethra R S · Answer

Hi Pravin, Thats fine. We can write 3 different commands in single proc but what i want to know is, Is it a good practise ?

Pravin More · Answer

Hi nethra... its depend on you situation where you are using it.....i mean are you using these 3 opration on same row....or any random record.....it dont have problem if its random records.........it will be done by parameter to sproc. make your sproc genralized so that it will work good for any of above operaion you can do it by if else in sproc n pass param accordingly........ for more information please go through my below discussion..... http://www.c-sharpcorner.com/Forums/Thread/139906/single-stored-procedure-for-updatedelete-and-insert.aspx here i explained way do it.... Thanks, Pravin.

Single stored procedure for insert update delete,is it good or bad?

Answers (17)