TSQL templates

A twitter conversation inspired this post. @sqlrunner and @MladenPrajdic were discussing the tweet "Mandate: Name of the developer who creates/updates a stored procedure. No anonymous procedures should be allowed."

While nothing fancy, I have had these two set up in my TextPad clip library for ages. If I need to create a new proc/fxn, this gets me started. What I think is important about this is these are the headers. Much like I've said before, the undocumented code is not worth reading. Beyond tombstone information (method name, author and date), the purpose of the method can help so much in understanding the intention of a new codebase. The Recordset portion can be more trouble than it is worth but queries for SSRS can be worth the trouble to call out the elements being returned in case they are aliased "funny." Side-effects are obviously handy in stored procs

TSQL Procedures template

IF EXISTS
(
    SELECT
        SO.*
    FROM
        dbo.sysobjects SO
    WHERE
        SO.id = OBJECT_ID('dbo.<TEMPLATE>')
        AND OBJECTPROPERTY(SO.id, 'IsProcedure') = 1
)
BEGIN
    PRINT 'Dropping stored procedure dbo.<TEMPLATE>'
    DROP PROCEDURE dbo.<TEMPLATE>
END
PRINT 'Creating stored procedure dbo.<TEMPLATE>'
GO
-----------------------------------------------------------------------------
-- Function: dbo.<TEMPLATE>
-- Author: Full name
-- Date: yyyy-mm-dd
--
-- This procedure ...
--
-- Recordsets:
-- None
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- yyyy-mm-dd username
--
-----------------------------------------------------------------------------
CREATE PROCEDURE dbo.<TEMPLATE>
(
)
AS
BEGIN
    SET NOCOUNT ON

END
GO
PRINT 'Granting rights for dbo.<TEMPLATE>'
GRANT EXECUTE ON dbo.<TEMPLATE> TO []
GO

TSQL Function template

This is the template I work from for developing functions.

IF EXISTS
(
    SELECT
        SO.*
    FROM
        dbp.sysobjects SO
    WHERE
        SO.id = OBJECT_ID('dbo.<TEMPLATE>')
        AND OBJECTPROPERTY(SO.id, 'IsDeterministic') IN (1, 0)
        AND OBJECTPROPERTY(SO.id, 'IsView') = 0
)
BEGIN
    PRINT 'Dropping function dbo.<TEMPLATE>'
    DROP FUNCTION dbo.<TEMPLATE>
END
PRINT 'Creating function dbo.<TEMPLATE>'
GO
-----------------------------------------------------------------------------
-- Function: dbo.<TEMPLATE>
-- Author: Full name
-- Date: yyyy-mm-dd
--
-- This function ...
--
-- Recordsets:
-- None
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- yyyy-mm-dd username
--
-----------------------------------------------------------------------------
CREATE FUNCTION dbo.<TEMPLATE>
(
)
RETURNS
AS
BEGIN
    

END
GO
PRINT 'Granting rights for dbo.<TEMPLATE>'
GRANT SELECT ON dbo.<TEMPLATE> TO []
GO

Some real-world examples of what this would look like are below

-----------------------------------------------------------------------------
-- Function: dbo.CareerDevelopmentConferenceAdvisorProgramLoad
-- Author: Bill Fellows
-- Date: 2008-05-07
--
-- This procedure will load the data into the advisor program table.
-- In essence, this records the current progress of an advisor in the CDC
-- program.  This does not touch the event history table as a seperate process
-- handles that.
--
-- Recordsets:
-- None
--
-- Side-effects:
-- Existing CDC program records will be purged from SR_M_ADV_PGM_S
-- New CDC program records will be added to SR_M_ADV_PGM_S
--
-- See also:
-- dbo.CareerDevelopmentConferenceAdvisorProgramCandidateGet
--
-- Modified:
--
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Function: dbo.AltPayeeReportingPeriodCandidateGet
-- Author: Bill Fellows
-- Date: 2009-07-16
--
-- This function will identify "new" reporting period dates in the altate payee
-- reporting table.  It will do this by finding periods that do not exist in
-- the advisor reporting table and returning the oldest.  The assumption being
-- if there are two rows present, the newest is currently in the process of
-- being populated.
--
-- Recordsets:
--  adv_id Advisor id
--  acct_idn Account number
--  rptg_prd_dt Reporting period
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-- 2009-08-20 bfellows
-- Due to systematic redemption business rule, we should only encounter 0-2
-- reporting periods in the redemption table that do not exist in the advisor
-- reporting table.  The loading of the "other" reporting tables depends on the
-- FOO process populating shareholder account balance.  With all of that
-- in mind, this will only return the oldest reporting period date of the two
-- potential rows.
--
-- 2009-10-05 bfellows
-- Logic incorrect on determining candidates.
-----------------------------------------------------------------------------

Final cautionary note, don't trust comments in source code. They may be out of date, written by a habitual liar, sociopath, etc. Instead, treat it like Garmin/Google/MapQuest navigation directions---they are for planning purposes only. The only truth lies in the code, so get out your lamp and start looking.