Microsoft SQL Server 2012 Bible

Managing Stored Procedures

The actual management of stored procedures is simple compared to the logic within them. When you know the basic facts and syntax, managing stored procedures shouldn't present any problems.

All code samples use the AdventureWorks2012 database, available at http://msftdbprodsamples.codeplex.com/.

Create, Alter, and Drop

Adding, modifying, and removing a stored procedure within the database is a straightforward process. You can use three basic Data Definition Language (DDL) commands: CREATE, ALTER, and DROP.

Create

The CREATE statement must be the first statement in a batch, and the termination of the batch ends the stored procedure definition. In other words, everything between the CREATE PROCEDURE statement and the next batch terminator is considered part of the stored procedure definition. 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 prefix the name of a stored procedure. These are reserved for system stored procedures, covered later in this chapter.
• Use a standard prefix for the stored procedure name, such as usp, Proc, p, and so on. The prefix 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 appropriate schema.
• Use descriptive names for stored procedures. ...