Is one Store procedure for all dml satatement best?

Question

Hi Friends My project is depend with database for lot of implementation. i have many tables that is need to be insertedupdatedselected and delete the record. i want to create one single procedure for all dml funtions(insertupdatedeleteselect).If i creating this one procedure for all dml function then no need to create a more procedure for each and everything. and it reduce code also. Is My method is correct for implementation or i need to create separate procedure for all dml funciton likewise all table. please help me . Which is the best?

Akkiraju Ivaturi · Answer

Do not go for this kind of implementation unless the project is very small and no more changes required in future. This is a bad practice which introduces lot of dependency in code. Always modularity is beneficial for development.

Sapan Singh · Answer

Hi If you create one single procedure for all DML operation.It can reduce your number of procedure .But it will be very complex and difficult to mange. My advice to you Create single procedure for DML operation. One thing more You can create single procedure for Insert and update operation and it will distinguish by Primary Key or any other flag. ex. Create Procedure Customer_InsertUpdate @Custid int @CustName varchar(100) As if(@Custid=0) begin -- insert statement end else begin -- Update Statement end

Jignesh Trivedi · Answer

Hi It is good idea to make only one sp. but what about Maintainability? i think  it is very Difficult to maintain it. My opinion is go with individual SP and also make common function that accept name and parameter of SP it help to remove duplicate code also. hope this will help you.