I'll typically place a few unit test examples in the comment block of each stored procedure I write. Sometimes I'll use SQLProfiler to cature sample procedure calls while the QA tests the application, and then save that off as a script for unit testing, mostly just as a smoke test to make sure I havn't broken any dependencies whenever I make a change to a procedure, view, or table.

Also, whenever I make a change to a stored procedure that returns a result, if the result is not expected to change, perhaps something like a performance optimization, then I'll save a before and after text copy of results into a comparison tool like WinMerge and comfirm nothing unexpected like sort order, doubling up of rows, or NULL columns has changed.