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, >
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
--Insert code here
IF @localTran = 1 AND XACT_STATE() = 1
COMMIT TRAN LocalTran
END TRY
BEGIN CATCH
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)
END CATCH
END
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.
- 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.
Archived WordPress comments (9)
Historical comments from the original site; this archive is read-only.
in addition to the set options you have listed, i have used as a template the ones for indexed views
SET QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
http://msdn.microsoft.com/en-us/library/ms175088.aspx
2011-07-11 14.19 Done!
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
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.
Cheers
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).
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?
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
Gianluca