Document your stored procedures internally

I find it helpful to document the intent for stored procedures within their body, and to also document changes to the stored procedure with a date stamp and a ticket number. This makes it easier for future maintainers.

CREATE PROCEDURE dbo.SOMETHING_SAVE
 @something_id int,
 @name varchar(50)
AS

/*
 Does blah blah
 -- Changed on 2006-09-07 to resolve ticket #570
*/

 -- Do stuff

GO
[download]

Treat your stored procedures as any other asset - keep them in a versioning system

Your schema and stored procedures should always be recorded in a versioning system. If you find yourself needing to use a version of your application from 2 years ago, you need to be able to recreate the database as it was at that time.

Additionally, when moving from a development to production environment you need to be able to easily identify the changes that have been made.

Privilege Separation

I prefer to only access the data through stored procedures (and possibly views). With some databases you can revoke all table permissions, and grant selective permissions to individual SPs/Views. This works because of 'Ownership Chaining'.

By doing this you can restrict the level of access each database user is granted to a much finer level. Different interfaces into the system (e.g. admin vs external user) can have access to specific procedures which adhere to business logic.

Another way I like to use this is to have password verification be a black box.

$sql = q{EXEC AUTH_USER @username = ?, @password = ?};
my ($user_id) = $dbh->selectrow_array($sql,{},$username,$password);
[download]

And finally, it overs an additional protection from SQL Injection. If they can't touch the table, they are less dangerous.

Naming conventions

I like is to reverse the normal naming scheme, and use NOUN_VERB.

Examples: SOMETHING_BY_ID, SOMETHING_SAVE, SOMETHING_LIST. I often add an extra level to that by prefixing the name with an abbreviation of the intended interface. e.g.:

• PORTAL_SOMETHING_LIST
• PORTAL_SOMETHING_BY_ID
• ADMIN_SOMETHING_LIST
• ADMIN_SOMETHING_BY_ID
• ADMIN_SOMETHING_SAVE

I find that this keeps related items grouped when ordered alphabetically.

Benchmark the stored procedures

Make use of whatever tools are available for your database. For MS SQL2000 there is Query Analyer, which is a wonderful tool if you enable the 'Show Execution Plan' option.

The profiler tool is also useful for identifying bottlenecks.

Solutions (MS SQL2000 centric) to common reasons against stored procedures

Dynamic ordering

SQL Server 2000 does not directly support dynamic ordering, but you can emulate it with a CASE statement.

An important thing to remember is that it will not intelligently switch what datatype is being compared, so if some of the fields are char and some are integer, it will try to make them all integer - and cause an error

To avoid this you need to have multiple CASE statements, with the WHEN clauses grouped by data type.

order by 
  CASE @order
    WHEN 'name' THEN lastname
    WHEN 'state' THEN state
   END,
  CASE @order
    WHEN 'name' THEN firstname
  END,
  CASE @order 
      WHEN 'user_id' THEN user_id
  END
[download]

Dynamic Where clauses

Make use of boolean shortcircuiting.

CREATE PROCEDURE [dbo].[dynamic_example]
  @lastname varchar(50) = NULL,
  @firstname varchar(50) = NULL
AS

SELECT * from Users
WHERE (@lastname IS NULL OR users.lastname LIKE @lastname)
AND (@firstname IS NULL OR users.firstname LIKE @firstname)
GO
[download]

EXEC dynamic_example @firstname = 'chris%'
[download]

Update - moved dynamic paging to a reply, instead of linking to my perlmonk.org account