Document your stored procedures

I tend to put the docs just above the CREATE PROCEDURE statement. I also do not include the dates and descriptions of changes, that's what the source control software is for. As I keep all stored procedures as separate files in Perforce and do use the changelist descriptions I do not feel the need to repeat the same info elsewhere. If I need the history I know where to look.

Identifying changes for promotion

While I do keep the procs in Perforce and do have separate Dev, QA and Prod branches I usualy do not use this when generating the update scripts. I run a service on the servers indexing (generating MD5 hashes of) the objects (pages, dlls, executables, stored procs, views, schemas of tables, ...) and whenever preparing the update simply compare the indexes and pack the differing files.

Naming conventions

I like to be able to read the names of the procs. And since the stored procs are commands they should IMHO have the form of a command sentence. FetchThis, InsertThat, UpdateSomething, DeleteWhatever, ExpireJobs, ExportSites, ImportSitePostingParameters, GetSiteName, ...

The most common verbs are Fetch = return a recordset with some data, Get = return a few pieces of info in OUTPUT params, Insert = insert a new row to a table and return the generated ID, Update = update a row (usually using the ID), Set = insert or update something that doesn't have its own generated ID (like some additional details for some object), Delete = delete something.

Dynamic Where clauses

As explained in Re^3: Moving SQL from perl to Stored Procedures - MSSQL tricks, dynamic paging it's most efficient, even if inconvenient, to branch out and write several SELECT statements.