My stored procedure code template

Do you use code templates in SSMS? I am sure that at least once you happened to click “New stored procedure” in the object explorer context menu.

The default template for this action is a bit disappointing and the only valuable line is “SET NOCOUNT ON”. The rest of the code has to be heavily rewritten or deleted. Even if you use the handy keyboard shortcut for “Specify values for template parameters”  (CTRL+SHIFT+M), you end up entering a lot of useless values. For instance, I find it very annoying having to enter stored procedure parameters definitions separately for name, type and default value.

Moreover, one of the questions I see asked over and over in the forums at SqlServerCentral is how to handle transactions and errors in a stored procedure, something that the default template does not.

Long story short, I’m not very happy with the built-in template, so I decided to code my own:

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <ProcedureName, sysname, >

    DECLARE @localTran bit
    IF @@TRANCOUNT = 0
        SET @localTran = 1


        --Insert code here

        IF @localTran = 1 AND XACT_STATE() = 1
            COMMIT TRAN LocalTran


        DECLARE @ErrorMessage NVARCHAR(4000)
        DECLARE @ErrorSeverity INT
        DECLARE @ErrorState INT

        SELECT  @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE()

        IF @localTran = 1 AND XACT_STATE() <> 0
            ROLLBACK TRAN

        RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)



This template can be saved in the default path and overwrite the kludgy “New Stored Procedure” built-in template.

Some things to keep in mind:

  • I don’t use nested transactions (they’re totally pointless IMHO) and I check for an existing transaction instead.
  • The stored procedure will commit/rollback the transaction only if it was started inside the procedure.
  • I want every stored procedure to throw the errors it catches. If there’s another calling procedure, it will take care of the errors in the same way.
A couple of words on the template parameters:
  • This is your computer: you can safely replace <Author, ,Name> with your real name.
  • It would really be nice if there was some kind of way to make SSMS fill <Create Date, ,> with the current date. Unfortunately there’s no way. If you are using CVS or some other kind of version control system, this is a nice place for an RCS string such as $Date$
  • If you like templates parameters and you heard bad news regarding this feature in the next version of SQL Server (codename Denali), don’t worry: MS fixed it.
EDIT: 2011-07-08 18:10 Mladen Prajdic (blog|twitter) just pointed out that it had no “SET XACT_ABORT ON” at the top. Fixed!

Posted on July 8, 2011, in SQL Server, SQL Server Central, T-SQL and tagged , , , . Bookmark the permalink. 9 Comments.

  1. great script, thanks for posting it

    in addition to the set options you have listed, i have used as a template the ones for indexed views


  2. Thank you, Robert! I’ll edit the template to incorporate your valuable suggestions.
    2011-07-11 14.19 Done!

  3. you don’t need to use @@trancount at all – xact_state() has everything you need
    plus on entry you need to check if (xact_state() < 0) raiserror('uncommitable tran found', 16, 1) and you need to output the error message using raiserror(@msg, 0, 1) to make sure all the errors caught by every catch block in the stack shows up – world of pain otherwise
    and your throw code needs some work – google it

    • Thanks for your input, Jack.
      You’re right – what @@trancount does is completely covered by XACT_STATE(), you can use any of those.
      Early detection of doomed transactions is a good suggestion. They would be caught anyway by the CATCH block, but I agree that checking for XACT_STATE on entry would save some work.
      Raising errors with severity 0 isn’t any different from using PRINT and doesn’t cause falling into the catch block of the calling procedure, I don’t see how that coud help. Maybe I’m missing something, if you have a link to your blog or to someone else’s code to clrify this suggestion, I’m interested in taking a look.
      On this particular subject, please consider that some (older) libraries such as ADO are unable to handle more than one error message and raising multiple errors would make the app ignore any transformation on the message you can do at higher levels in the call stack.
      I don’t uderstand exactly what kind of work would this throw code require, other than updating to SQL2012’s throw command. Again, suggestions and references are welcome on this subject.

  4. Nice template.The problem with TRY..CATCH I’m having is that there can occur an UNCOMMITABLE STATE within the CATCH block which prevents write operations, including error logging, until the transaction is rolled back. That presents a problem from a nested procedure that didn’t start the transaction and shouldn’t roll it back. The issue with an example is presented in this SO question:

    • The problem with the prodedure you posted on SO is that the error code and message are tested AFTER attempting the ROLLBACK. If you test the error message before attempting the rollback, the original message should be preserved.
      As far as logging it, the only options you have with uncommittable transactions is the use of table variables or logging to ERRORLOG (it’s a file, no transaction processing here).

  5. Great template.
    Only comment I have is that it doesn’t look possible to run any code outside of a local transaction (LocalTran).
    Even if you set @localTran = 0 its still beginning LocalTran which means it has to be committed or rolled back.
    I realise everything in SQL Server runs within an implicit transaction, but if you just wanted to run something say a SELECT do you care if it’s in a transaction or not?

    • Hi Adrian,

      You don’t need a transaction if all you’re doing in the procedure is execute a SELECT statement. However, code tends to change over time and it’s better to have the transaction thing sorted right from the start rather than having to remember that when the code needs to be changed and you introduce something that writes to a bunch of tables.
      At least, this is how I see it.

      Regarding setting @localTran to avoid starting the transaction, that’s not what the code is supposed to do. A transaction is started if is hasn’t already been started, that’s it.

      Hope this helps

  1. Pingback: Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: