Sample templates which can be used for any SQL Stored procedure is needed if you want to quickly develop stored procedures with proper error handling and transactional concurrency. Basically it is placeholder or blueprint upon which the actual logic can be built. Quite handy, so check it out!
[cc lang=”sql”]
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_standard_proc]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_standard_proc]
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Purpose: To create sample template
Usage: exec usp_standard_proc
Author:
Created Date:
Modification Versions: Base Version : Added by on for the
Modified Version : Added by on for the
*/
CREATE PROCEDURE [dbo].[usp_standard_proc]
AS
BEGIN
SET nocount ON;
DECLARE @ProcName varchar(255)
SELECT @ProcName = ‘usp_standard_proc’
DECLARE @trancount INT;
SET @trancount = @@trancount;
BEGIN try
IF @trancount = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION usp_standard_proc;
— UNCOMMENT BELOW TO DISPLAY DIFFERENT ERROR MESSAGES
/*
SELECT CAST(‘RAM’ AS INT)
SELECT 1/0
*/
SELECT GETDATE()
IF @trancount = 0
COMMIT;
END try
BEGIN catch
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@XactState INT
— Assign variables to error-handling functions that capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ‘-‘),
@XactState = XACT_STATE()
— Build the message string that will contain original error information.
SELECT @ErrorMessage =
N’Error %d, Level %d, State %d, Procedure %s, Line %d, ‘ + ‘Message: ‘+ ERROR_MESSAGE();
IF @XactState = -1
ROLLBACK;
IF @XactState = 1
AND @trancount = 0
ROLLBACK
IF @XactState = 1
AND @trancount > 0
ROLLBACK TRANSACTION usp_standard_proc;
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
)
END catch
END
GO
[/cc]
