Sql Server Stored Procedure Template

Summary

Have you ever written that “perfect” piece of code to handle some situation? You spend hours reading blogs and other code, and you finally pull it all together in a version that works in your project. In your mind, you know that since you just spent a few hours on this, there is no way you are going to forget it. In a future project when you will need this same code, you will just “remember” it was in xyz project. Well, for me, I start out that way, but I soon realize that I can’t remember where I did it. To my credit, I usually at least remember that I did it, I just don’t know where. While I love BareGrep as much as anybody, it is a bit like Google. If you don’t enter the right search terms, you still don’t find it.

So this post (and future posts like it) are an attempt to put that code in “one place” where I hope to be able to find it down the road. If the chunk of code is not worth a blog post, then it must not have been that impressive after all. 🙂

This morning, I was trying to to implement error handling in my catch block. I found a chunk of code I had used in the past, and it was rolling back transactions, which reminded me that I had forgot to put that in the current sproc I was working on. At that point, I started this post. Below are some notes that should be helpful to me in the future. I hope they will benefit you as well.

Header Comment

Include some kind of comment. I include a PRINT statement, which helps in the SqlCmd (future post) script that calls this. This also includes the DROP/CREATE approach which I like to do for sprocs.

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
/*
  Revision History
    4Jul15  ScottB  Initial Version
*/
PRINT 'ProceduresCall_spt.sql'
 
SET ANSI_NULLS ON
GO
 
SET ANSI_NULLS ON
GO
 
-- http://technet.microsoft.com/en-us/library/ms190324(v=sql.100).aspx
IF (OBJECT_ID(N'call.Call_spt', 'P') IS NOT NULL ) BEGIN
   DROP PROCEDURE call.Call_spt
   PRINT '   Dropped';
END
GO
 
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE call.Call_spt
    @xml XML
AS
...

top

Initial Variables

Declare a couple variables at the very beginning that will be needed later. The @tranCount is needed to be able to commit and rollback transactions. The @tenantId is needed in the Error handler, so we set it outside the BEGIN TRY (not sure if this is necessary in TSQL, but it would be in C#).

1
2
3
4
5
6
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @tranCount INT = @@TRANCOUNT;
    -- Default to System, but it should get reset below.
    DECLARE @tenantId INT = 1;

top

BEGIN TRANSACTION ?? / COMMIT

As close as possible to your actual Insert or Update, you need a block like this. It handles the situation where we were already in a transaction (possibly from a calling sproc).

1
2
3
4
5
6
7
8
9
10
11
IF (@tranCount = 0) BEGIN
   BEGIN TRANSACTION
END ELSE BEGIN
   SAVE TRANSACTION Call_spt
END
 
-- Insert or Update ...
 
IF (@tranCount = 0) BEGIN
   COMMIT TRANSACTION
END

top

Catch Block

This next block is assuming that the prior code was in a TRY CATCH block.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
BEGIN CATCH
 
    DECLARE @xstate INT = XACT_STATE();
    IF @xstate = -1 BEGIN
       ROLLBACK TRANSACTION
    END
    IF @xstate = 1 AND @tranCount = 0 BEGIN
       ROLLBACK TRANSACTION
    END
    IF @xstate = 1 AND @tranCount > 0 BEGIN
       ROLLBACK TRANSACTION Call_spt
    END
 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();       
 
    EXEC tenant.ReThrowError_spt @tenantId
 
    RETURN -1;
END CATCH  

top

Print Friendly, PDF & Email