Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Template Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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

Post #1505552
Posted Thursday, October 17, 2013 3:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,538, Visits: 10,411
Maybe you can turn it into an article for SSC?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse