July 7, 2011 at 1:29 am
Hi,
We're running Sql Server 2008 and I'm looking to put forward a standard template for all stored procedures to the developers on my team and I was hoping I could get some thoughts/comments on the template which is below.
The template has the following requirements:
1. Maintainable going forward.
2. Transaction rollback capabilities.
3. Error details written out to screen.
4. Error details written to ErrorLog table (see attached script to create table).
Thanks in advance.
use <DatabaseName>
go
set ansi_nulls on
go
set quoted_identifier on
go
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[usp_StoredProcedureTemplate]') and type in (N'P', N'PC'))
drop procedure [dbo].[usp_StoredProcedureTemplate]
go
create procedure [dbo].[usp_StoredProcedureTemplate]
as
/*********************************************************************
Object Name:: usp_StoredProcedureTemplate
Author::
Contact Details::
Creation Date::
Version:: v1.0
Description:: Sql Server 2008 Stored Procedure Template
Called By:: N/A
*********************************************************************/
begin try
begin transaction
set nocount on
--Insert code here.
commit
end try
begin catch
if (xact_state()) <> 0 rollback transaction;
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[ErrorLog]') and type in (N'U'))
begin
insert into [dbo].[ErrorLog]
(
[DatabaseName],
[ExecutingUser],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage],
[ExecutionTimestamp]
)
select
db_name() [DatabaseName],
system_user [ExecutingUser],
error_number() [ErrorNumber],
error_severity() [ErrorSeverity],
error_state() [ErrorState],
error_procedure() [ErrorProcedure],
error_line() [ErrorLine],
error_message() [ErrorMessage],
convert(varchar(24), getdate(), 113) [ExecutionTimestamp]
end
else
begin
declare @ErrorWarning varchar(500)
set @ErrorWarning= 'The table [dbo].[ErrorLog] is not present on the Sql Server instance [' + @@servername + '] therefore the error details could not be logged.'
raiserror(@ErrorWarning, 10, 1)
end
declare @ErrorMessagenvarchar(4000)
declare @ErrorSeverityint
set @ErrorMessage = error_message()
set @ErrorSeverity = error_severity()
raiserror(@ErrorMessage, @ErrorSeverity, 1)
end catch
go
www.sqlAssociates.co.uk
July 7, 2011 at 2:50 am
This is the template I would use:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <ProcedureName, sysname, >
AS
BEGIN
SET NOCOUNT ON;
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() = 1
ROLLBACK TRAN
-- Don't log if there's an outer transaction,
-- let the caller log the error
IF @localTran = 1
BEGIN
INSERT INTO [dbo].[ErrorLog] (
[DatabaseName],
[ExecutingUser],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage],
[ExecutionTimestamp]
)
SELECT DB_NAME() [DatabaseName],
SYSTEM_USER [ExecutingUser],
ERROR_NUMBER() [ErrorNumber],
ERROR_SEVERITY() [ErrorSeverity],
ERROR_STATE() [ErrorState],
ERROR_PROCEDURE() [ErrorProcedure],
ERROR_LINE() [ErrorLine],
ERROR_MESSAGE() [ErrorMessage],
-- WHY store ad datetime as varchar???
GETDATE() [ExecutionTimestamp]
END
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply