Database Reference
In-Depth Information
C H A P T E R 6
Using Stored Procedures
A
stored procedure
is a collection of SQL statements that allows you to perform a task repeatedly. You can
create the procedure once and reuse it any number of times in your program. This can improve the
maintainability of your application and allow applications to access the database in a uniform and
optimized manner. The goals of this chapter are to get you acquainted with stored procedures by
creating and modifying them in SQL Server 2012 and to explain how C# programs can interact with
them. This chapter covers the following:
•
Creating stored procedures
•
Modifying stored procedures
•
Displaying the definitions of stored procedures
•
Renaming stored procedures
•
Working with stored procedures in C#
•
Deleting stored procedures
Creating Stored Procedures
Stored procedures can have
parameters
that are used for input or output. Stored procedures can have a
single integer
return value
(which defaults to zero) and can return zero or more result sets. They can be
called from client programs or other stored procedures. They are powerful indeed and are becoming the
preferred mode for much database programming, particularly for multitier applications and web
services, since (among their many benefits) they can dramatically reduce network traffic between clients
and database servers.
Note
If you are using the AdventureWorks2008R2 database, then a few table name changes will apply. For
example, in my code example, I used the Person.Contact table, which is actually named Person.Person in
AdventureWorks2008R2. If you come across any such instance, replace Person.Contact with Person.Person.