Rules for Stored Procedures

The biggest mistake anyone can make in naming stored procedures is to prefix them with sp_. This generally happens with developers who come from VB or VC background, where they get used to prefixing objects with their type.

Note

You will find stored procedures within SQL Server that start with sp_ but these are reserved for system stored procedures. We will look at them in Chapter 6.

So how do we name our stored procedures? There are different naming standards and methods that we can employ. However, a commonly followed one is to prefix the stored procedure with what it does. If a stored procedure inserts data, then it will be prefixed with ins_, if it's returning rows of data then we will use sel_, and so on. This naming convention is for main stored procedures that are used as part of the system. When it comes to writing procedures to be used for supporting the system, then we may prefix the procedure with util_ for utility, or sup_ for support. Note that following this convention, triggers would be prefixed with tr_, and user defined functions with fn_.

If you are using a common database for several applications, then prefix stored procedures for common functionality with the name of the database, and ones specific to a separate system with a pseudonym for that system. For example, if northwind has a procedure that accesses pubs, then we will name it as pub_sel_Authors.

Note

This naming convention is needed only when cross-database or cross-system functionality is included.

Other rules include not using spaces in the name, or any ‘specialized’ characters such as %, &, and so on. Underscores, _, are allowed, and are the preferred method of naming tables and columns rather than using spaces. If you do wish to use a space instead, then while working with the object you will need to surround the name with square brackets.

Most procedures have parameters passed to them. It is vital that every parameter is tested to ensure that its value is within a valid range, especially in stored procedures for updation, where a parameter is used as part of the WHERE row filter criteria, if an invalid value is submitted we can end up updating every row in the table. Of course, it is impossible to check for a wrong value that unfortunately fits in the range.

One of the problems with stored procedures is the lack of comments within them. Even the simplest of stored procedures should have comments. Every stored procedure should also contain a quick description of itself, at the top. A more complex code should be properly commented throughout and the comments should be up to date.

When developing a stored procedure, we should keep a record of it in some source control system, like Microsoft Visual SourceSafe. The latest version will always be within our database, but what happens if we release the latest version and there is a problem? How can we rollback quickly to a previous version of the code? You may also develop an enhancement to the system, but may have to fix a bug also. Source control can help us here, as it allows us to easily access the production version of the stored procedure, make the change, document it, and perhaps even branch the code, so that the whole process of bug fixing while developing is completed in a secure environment.

Note

If you use the source control software, ensure that it resides on a drive that is backed up nightly.

The source control system may also be capable of automatically updating comments defining the version, the date and time of the last check-in. This can prevent rogue updates to the stored procedure code, which hasn't gone through the source control system, but it is not foolproof. It is possible to create a system stored procedure capable of checking this comment value, with a value entered within a system table when the stored procedure was created.