SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Template


Stored Procedure Template

Author
Message
DataDog
DataDog
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 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


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59859 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search