Stored Procedures vs. Direct SQL

Stored Procedures vs. Direct SQL

Most code fragments shown in this document use SqlCommand objects to call stored procedures to perform database manipulation. In some cases, you will not see the SqlCommand object because the stored procedure name is passed directly to a SqlDataAdapter object. Internally, this still results in the creation of a SqlCommand object.

You should use stored procedures instead of embedded SQL statements for a number of reasons:

• Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
• Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
• Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
• Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
• Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

The SQL Server online documentation strongly recommends that you do not create any stored procedures using "sp_" as a name prefix because such names have been designated for system stored procedures. SQL Server always looks for stored procedures beginning with sp_ in this order:

1. Look for the stored procedure in the master database.
2. Look for the stored procedure based on any qualifiers provided (database name or owner).
3. Look for the stored procedure, using dbo as the owner if an owner is not specified.

http://msdn.microsoft.com/en-us/library/ms810829.aspx