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