Category Archives: Best Practices

Standard template for SQL Stored Procedures

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!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
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