Salmon Training Online Books: Sample Chapter

Contents

• 1. Introduction
• 2. Development Database
• 3. First Stored Procedure
  • Introduction
  • Requirements Analysis
  • Table Creation
  • Adding Test Data
  • Coding the Transact-SQL
  • Creating the Procedure
  • Executing the Procedure
  • Examining Procedures
  • Rules for Procedures
  • Input Parameters
  • Default Parameters
  • The Return Code
  • Output Parameters
  • Conclusion
• Design
• Insert Procedures
• Modularization
• Error Processing
• Program Flow
• Update Procedures
• Transactions
• Temporary Tables
• Triggers
• Performance Tuning
• Security

3.9 Rules for Creating Stored Procedures

Stored procedures are database objects and their names must conform to the same rules as other SQL Server database objects e.g. table names. They mustn't start with a number, mustn't include a space, mustn't be a reserved keyword etc.

Just where is the stored procedure? The procedure text is stored within the database in which it was created. If you are calling the stored procedure from a different database, prefix the stored procedure name with the database in which it was created - note the two dots in the listing (alternatively use exec pubs.dbo.get_product).

exec pubs..get_product

It's not unusual to see a company naming convention that gives all stored procedures the same prefix: prc_ is a common choice. We won't be applying a stored procedure prefix in this book. But we do advise on one guideline for naming your stored procedures: don't use the prefix sp_ at the begriming of your procedure name. The system procedures (lie sp_helptext) start with sp_ for a reason: using this prefix makes SQL Server look in a special system database for the procedure before it looks in the current database. The prefix should be reserved for system procedures.

• next >>
• << previous
• Developing Stored Procedures for Microsoft SQL Server (no ads in full eBook)