template | Bryan Avery

Everywhere I go from client to client I find that when developers create Stored Procedures that just perform the task in hand, and I so often find that no exception handling, comments or transaction based handling has been put in place. So with all this in mind I set to write a structured Stored Procedure to be used as a template for future Stored Procedures that I create.
-- =============================================
-- Author: Bryan Avery
-- Create date: 15th July 2008
-- Description: Import the Taxonomy in to WPPs
-- =============================================
ALTER PROCEDURE [dbo].[sp_WaSPs_TaxonomyImport] 
 @Debug bit = 0,
 @ErrorMessage varchar(2000) OUTPUT, 
 @return_code Int OUTPUT

AS
BEGIN
-- ========================================================
-- Declare SProc variables 
-- ========================================================

DECLARE @Error int
 DECLARE @RowCount int

-- ========================================================
-- Initialise SProc variables 
-- ========================================================
 
 SET NOCOUNT ON;

-- ========================================================
-- SProc content 
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
DECLARE @Debug_ProcStartTime datetime 
, @Debug_ProcEndTime datetime 
 
SET @Debug_ProcStartTime = Getdate() 
 
SET NOCOUNT OFF 
 
 PRINT '' 
 
 PRINT '-- ======================================================' 
 PRINT '-- = PROCEDURE sp_WaSPs_TaxonomyImport' 
 PRINT '-- = @Debug = ' + IsNull(Cast(@Debug as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 
 PRINT '' 
 
 
END

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * BEGIN TRANSACTION' 
 PRINT '-- ******************************' 
 
END

-- ========================================================
-- Uncomment the section below if you want to remove all Taxonomy information from the application
-- ========================================================

-- DELETE tbl_WaSPs_GroupArea;
-- DELETE tbl_WaSPs_AreaUserName;
-- DELETE tbl_WaSPs_AreaSite;
-- DELETE tbl_WaSPs_Areas;
-- 
-- DELETE tbl_WaSPs_SiteSite;
-- DELETE tbl_WaSPs_Sites;


BEGIN TRANSACTION


BEGIN TRY

-- ========================================================
-- Update the areas
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Update the Areas' 
 PRINT '-- ******************************' 
 
END

UPDATE tbl_WaSPs_Areas
 SET AreaName = et2.EntityType, EntityTypeCD = et2.EntityTypeCD, myView = 'False'
 FROM WaspFactory.dbo.tblEntityType et2, tbl_WaSPs_Areas a1
 WHERE et2.EntityTypeCD = a1.EntityTypeCD 
 
-- ========================================================
-- Insert the areas
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Insert the Areas' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_Areas(AreaName, EntityTypeCD, myView)
 SELECT et2.EntityType, et2.EntityTypeCD, 'False'
 FROM WaspFactory.dbo.tblEntityType et2
 WHERE et2.EntityTypeCD NOT IN (SELECT EntityTypeCD FROM tbl_WaSPs_Areas)

-- ========================================================
-- Update the sites
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Update the Sites' 
 PRINT '-- ******************************' 
 
END

UPDATE tbl_WaSPs_Sites
 SET title = e1.EntityName, siteid = e1.EntityIdent, entityid = e1.EntityID
 FROM WaspFactory.dbo.tblEntity e1, tbl_WaSPs_Sites s1
 WHERE e1.EntityName IS NOT Null AND
 e1.EntityId = s1.entityid

-- ========================================================
-- Insert the sites
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Insert the Sites' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_Sites(title, siteid, entityid)
 SELECT EntityName, EntityIdent, EntityID
 FROM WaspFactory.dbo.tblEntity
 WHERE EntityName IS NOT Null AND
 EntityId NOT IN(SELECT entityid FROM tbl_WaSPs_Sites)

-- ========================================================
-- Remove all the links first
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Remove all the links first' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_wasps_AreaSite
 FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
 tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
 WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
 Sites.EntityId = Entity.EntityId AND
 Entity.EntityTypeCD = EntityType.EntityTypeCD

-- ========================================================
-- Link the sites to the areas
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the sites to the areas' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_wasps_AreaSite(AreaId, SiteId)
 SELECT AreaId, Id
 FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
 tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
 WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
 Sites.EntityId = Entity.EntityId AND
 Entity.EntityTypeCD = EntityType.EntityTypeCD AND
 (AreaId NOT IN (SELECT AreaId FROM tbl_wasps_AreaSite) AND
 Id NOT IN (SELECT SiteId FROM tbl_wasps_AreaSite))
 
-- ========================================================
-- Remove all the site to site links
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Remove all the site to site links' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_WaSPs_SiteSite
 WHERE SiteId = fkSiteId

-- ========================================================
-- Link the site to themself
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the site to themself' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
 SELECT ID, ID, 'Neither'
 FROM tbl_WaSPs_Sites

-- ========================================================
-- Delete all the site to site links
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Delete all the site to site links' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_WaSPs_SiteSite
 FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
 WaspFactory.dbo.tblEntityLink el1, 
 WaspFactory.dbo.tblEntity e2, 
 tbl_WaSPs_Sites s2
 WHERE e1.EntityId = s1.EntityId AND
 s1.EntityId = el1.EntityID1 AND
 el1.EntityID2 = e2.EntityId AND
 e2.Entityid = s2.EntityId

-- ========================================================
-- Link the site to other sites
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the site to other sites' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
 SELECT s1.ID, s2.ID, 'Neither'
 FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
 WaspFactory.dbo.tblEntityLink el1, 
 WaspFactory.dbo.tblEntity e2, 
 tbl_WaSPs_Sites s2
 WHERE e1.EntityId = s1.EntityId AND
 s1.EntityId = el1.EntityID1 AND
 el1.EntityID2 = e2.EntityId AND
 e2.Entityid = s2.EntityId
 
END TRY 
 
BEGIN CATCH 
 
 SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT ; IF (@Error <> 0 OR @RowCount = 0) GOTO QuitWithRollback -- (check for error) 
 
END CATCH

-- ---------------------------------------------------------------------------------------------------- 
-- Commit Transaction 
-- ---------------------------------------------------------------------------------------------------- 
 
IF (@@TRANCOUNT > 0) 
 
BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * COMMIT TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 COMMIT TRANSACTION 
 
END

GOTO QuitWithSuccess

-- ---------------------------------------------------------------------------------------------------- 
-- Error Handling 
-- ---------------------------------------------------------------------------------------------------- 
QuitWithConcurrencyRollback: 
 
IF (@@TRANCOUNT > 0) 
 
 BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * ROLLBACK TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 ROLLBACK TRANSACTION 
 
 END 
 
GOTO QuitWithConcurrencyError

-------------------------------------------------- 
QuitWithRollback: 
 
IF (@@TRANCOUNT > 0) 
 
 BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * ROLLBACK TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 ROLLBACK TRANSACTION 
 
END 
 
GOTO QuitWithError

-------------------------------------------------- 
QuitWithConcurrencyError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithConcurrencyError' 
 PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @ErrorMessage = 'Concurrency Error' 
 
SET @return_code = 3 
 
RETURN(3)

-------------------------------------------------- 
QuitWithError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithError' 
 PRINT '-- = @Error = ' + IsNull(Cast(@Error as varchar),'<NULL>') 
 PRINT '-- = @return_code = ' + IsNull(Cast(@return_code as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @ErrorMessage = 'SQL Error' 
 
SET @return_code = 2 
 
RETURN(2)

-------------------------------------------------- 
QuitWithParameterError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithParameterError' 
 PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
 
SET @return_code = 1 
 
RETURN(1) 
------------------------------------------------ 
QuitWithSuccess: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithSuccess' 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @return_code = 0 
 
RETURN(0) 
-- -------------------------------------------------- 
END
StoredProcedure
Bryan Avery – Blog

Tag Archives: template

Structure to Stored Procedure

this and that
