433
Chapter 17: Developing Stored Procedures
17
Create
The CREATE statement must be the fi rst statement in a batch, and the termination of
the batch ends the stored procedure defi nition. In other words, everything between the
CREATE PROCEDURE statement and the next batch terminator is considered part of the
stored procedure defi nition. In SQL Server Management Studio (SSMS), the default batch
terminator is the word GO. When creating a stored procedure, consider a few best practices:■ (^) Never use sp_ to prefi x the name of a stored procedure. These are reserved for sys-
tem stored procedures, covered later in this chapter.
■ (^) Use a standard prefi x for the stored procedure name, such as usp, Proc, p, and so
on. The prefi x helps identify an object as a stored procedure when reviewing and
troubleshooting code.
■ (^) Always use a two-part naming convention, schema.objectname, when creating
stored procedures. This ensures that the stored procedure is added to the appropri-
ate schema.
■ (^) Use descriptive names for stored procedures. When there are hundreds, if not thou-
sands, of stored procedures in the database, it helps to have a name that describes
what the stored procedure does.
■ (^) Implement error handling in stored procedures. Syntax and logic errors should
be gracefully handled, with meaningful information sent back to the calling
application.
For more information on how to implement error handling, refer to Chapter 16, "Programming with
T-SQL."
The following example creates a basic stored procedure that selects the contents of the
Sales.Currency table from the AdventureWorks2012 database.
USE AdventureWorks2012
GO
CREATE PROCEDURE Sales.uspGetCurrencyInformation
AS
SELECT CurrencyCode, Name
FROM Sales.Currency;
GO
Alter
The ALTER statement modifi es the stored procedure. The benefi t of choosing to alter a
stored procedure rather than drop and re-create it is that the procedure is never removed
from the server. All object permissions are retained after an ALTER, whereas a DROP and
CREATE results in lost permissions.
c17.indd 433c17.indd 433 7/30/2012 5:39:34 PM7/30/2012 5:39:34 PM
http://www.it-ebooks.info