October 17, 2013 at 3:25 am
Over the years I have fine tuned my stored procedure template
It supports nesting and error handling and forms the basis of every SP I develop
I couldn't find a similar example on the net, so I am posting mine hoping it is useful and to get some feedback
go
create proc dbo.spTodo(@Msg varchar(255) output)
as
begin
begin try
set nocount on
set xact_abort on
set arithabort on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set quoted_identifier on
set concat_null_yields_null on
set numeric_roundabort off
declare @Return int = 0
declare @TranOwner bit = 0
if (xact_state() < 0) raiserror('uncommitable transaction found', 16, 1)
if (xact_state() = 0) begin
set transaction isolation level read committed
begin tran
set @TranOwner = 1
end--if
--> Logic goes here
label_commit:
if (@TranOwner = 1) begin
commit tran
end--if
return 1 --> Commit
label_rollback:
if (@TranOwner = 1) begin
rollback tran
end--if
return @Return --> Rollback
end try
begin catch
if (@TranOwner = 1 and xact_state() <> 0) begin
rollback tran
end--if
exec dbo.spThrowError
return 0 --> Error
end catch
end
go
and spThrowError:
go
create proc dbo.spThrowError
as
begin
declare @errmsg nvarchar(2048) = error_message()
declare @errno int = error_number()
declare @errseverity int = error_severity()
declare @errstate int = error_state()
declare @errline int = error_line()
declare @errproc nvarchar(126) = coalesce(error_procedure(), '-')
declare @newerrno int;
if (@errmsg not like N'spThrowError!%') begin
set @errmsg = N'spThrowError! Err: %d, Level: %d, State: %d, Proc: %s, Line: %d, Msg: ' + @errmsg
end--if
if (@errno in (1205, 3960)) begin
set @newerrno = 50000 + @errno
raiserror ( @newerrno
, @errseverity -- severity
, 1 -- state
)
end else begin
raiserror ( @errmsg -- msg
, @errseverity -- severity
, 1 -- state
, @errno -- template argument %d
, @errseverity -- template argument %d
, @errstate -- template argument %d
, @errproc -- template argument %d
, @errline -- template argument %d
)
end--if
end
go
October 17, 2013 at 3:48 am
Maybe you can turn it into an article for SSC?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply