SQL

Index
Search and Replace
Multiple Outer Joins
Comparing Dates

Procedures

Stored Procedure Template (Transact SQL)

What does it do?

It's a simple template that allows stored procedures to be written in a consistent format, including consistent transaction management.

Many stored procedures explicitly issue a begin tran at the start of their code, this isn't (in my opinion) the best way to handle transactions. If you have already begun a transaction, then starting a new one and rolling back will rollback all the way to the beginning of the first transaction. If you issue a save tran you will be able to rollback to the savepoint, which allows each stored procedure to be a self contained unit as well as part of a greater transaction.

Copyright

You may use this template in your own code, copyright free

Template

/**********************************************************************
 * SP Name:  my_sp
 * Author:   Wayne Jones
 * Date:     1 April 2020
 * DB(s):    MyDB
 *
 * Purpose:
 * Populates an unknown table with dummy data
 *
 * Date - Changed By
 * Changes
 **********************************************************************
 * 02/04/2021 - Wayne Jones
 * Took away all the useful code except the template
 *
 */

if exists( select * from sysobjects where name='my_sp' and type='P' )
	drop proc my_sp
go

create proc my_sp (
	@my_parm1        int = null
)
AS

/***************************
 ** Variable declarations **
 ***************************
 */

declare	@tc                 int    /* tran count */

/*******************************************************************
 ** Check whether to start a new transaction, or save the old one
 *******************************************************************
 */
 
select @tc = @@trancount
if ( @tc > 0 )
	save tran my_tran
else
	begin tran my_tran


/********************************************************************
 **                  PLACE YOUR PROCEDURE CODE HERE                **
 ********************************************************************
 */


/********************************************************************
 ** Transaction was successful
 ** - Commit if this procedure issued a begin tran
 ********************************************************************
 */

if ( @tc = 0 )
	commit tran my_tran

return 0

go

GRANT EXECUTE ON my_sp TO public

Notes

Change all references from my_sp to your own stored procedure name.
Whilst you can leave the transaction name the same, I would suggest renaming the transaction to [your procedure]_tran where [your_procedure] is the name of your procedure!
You will probably want to implement better security than granting the stored procedure to public!
You may wish to include the stored procedure comments in the procedure in which case just transfer the comment block below the closing bracket after the create proc statement.
Remember to check @@error after all insert, updates, deletes - rollback and return when an error occurs.

Suggestions

You may wish to use a text editor with templating functionality, to save editing time. There are many free and commerical text editors available with templating functionality, it all comes down to personal preference.

Details

Author:     Wayne Jones
Created:    1 May 2003
Works with: MS SQL Server, Sybase