TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Stored Procedures vs. Direct SQL
Shashi Ray
May 23, 2012
11
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
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:
Look for the stored procedure in the master database.
Look for the stored procedure based on any qualifiers provided (database name or owner).
Look for the stored procedure, using
dbo
as the owner if an owner is not specified.
Shashi Ray
Stored Procedures vs. Direct SQL
Next Recommended Reading
Difference between a View and Stored Procedure