Is it a good practice to use SQL Server stored procedures for INSERT, UPDATE, and DELETE statements?
Yes, anytime you pass a data modification request from a front-end application to SQL Server, it is a best practice to encapsulate the code in a stored procedure. Here are some of the reasons why:
- It is faster than using ADO methods to accomplish the same task.
- Network traffic can often be reduced because less code generally has to transverse the network.
- Often, stored procedures can be more efficient than Transact-SQL code sent to SQL Server not encapsulated in a stored procedure.
- It is often easier to debug and troubleshoot code in a stored procedure than when it is found mingled with code from the front-end application.
- It is also often much easier to write and maintain Transact-SQL code if it is in a stored procedure than when it is found mingled with code from the front-end application.