SQL 2005+ Store Proc Error Handling

--// Plop this in to query analyzer and select the DB of your choice, the F5, Enjoy.
GO

/******
Object: Table [dbo].[SQLProcErrorLogApps] Script Date: 08/28/2008 21:11:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLProcErrorLogApps](
[AppID] [
int] IDENTITY(1,1) NOT NULL,
[Application] [nvarchar](250)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](4000)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SQLProcErrorLogApps] PRIMARY KEY CLUSTERED
(
[AppID]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
)
ON [PRIMARY]

/******
Object: Table [dbo].[SQLProcErrorLog] Script Date: 08/28/2008 21:12:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLProcErrorLog](
[IDX] [
int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime]
NOT NULL CONSTRAINT [DF_SQLProcErrorLog_DateCreated] DEFAULT (getdate()),
[AppID] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_AppID] DEFAULT ((0)),
[UID] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_UID] DEFAULT ((0)),
[ErrNumber] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrNumber] DEFAULT ((0)),
[ErrSeverity] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrSeverity] DEFAULT ((0)),
[ErrState] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrState] DEFAULT ((0)),
[ErrProc] [nvarchar](126)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrLine] [
int] NULL CONSTRAINT [DF_SQLProcErrorLog_ErrLine] DEFAULT ((0)),
[ErrMessage] [nvarchar](2048)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrDBID] [
int] NULL,
[HostName] [nvarchar](128)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]

GO
ALTER TABLE [dbo].[SQLProcErrorLog] WITH CHECK ADD CONSTRAINT [FK_SQLProcErrorLog_SQLProcErrorLogApps] FOREIGN KEY([AppID])
REFERENCES [dbo].[SQLProcErrorLogApps] ([AppID])
GO
ALTER TABLE [dbo].[SQLProcErrorLog] CHECK CONSTRAINT [FK_SQLProcErrorLog_SQLProcErrorLogApps]

--

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Bob Mihada
-- Create date: 20080828
-- Description: Template for procs
-- ViewErrors: Select * from SQLProcErrorLog
-- =============================================
CREATE PROCEDURE [dbo].[myProcIsGreat]

AS
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@APPID
INT
,@UID
INT --// Any unique ID that can be used for additional debugging
,@ERRMSG NVARCHAR(2048)
,@ERRSEVERITY
INT
,@ERRSTATE
INT
,@ERRORID
INT
,@RC
INT
,@ROWAFFECTED
INT

SELECT
@APPID = 1 --// My Service
,@UID = -1 --// Nothing yet

--// Do Stuff

-- As an example I'm forcing an error
RAISERROR('Hi there, I am an error, nice to meet you.',16,1)

RETURN(321) --// My universal "All is good return code"
END TRY
BEGIN CATCH
SELECT
@ERRMSG = ERROR_MESSAGE()
,@ERRSEVERITY = ERROR_SEVERITY()
,@ERRSTATE = ERROR_STATE()

INSERT INTO SQLProcErrorlog(
AppID, UID, ErrNumber, ErrSeverity, ErrState, ErrProc, ErrLine, ErrMessage, ErrDBID, Hostname)
SELECT
@APPID
,ISNULL(@UID,-1)
,ERROR_NUMBER()
,@ERRSEVERITY
,@ERRSTATE
,ERROR_PROCEDURE()
,ERROR_LINE()
,@ERRMSG
, DB_ID()
, HOST_NAME()

RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)

RETURN(-1)
END CATCH


GO
exec('INSERT INTO [SQLProcErrorLogApps]([Application], Description) Values(''Testing Errors'', ''Blah blah blah, yakkity smakity and a tall glass of O.J.'')')
GO
EXEC myProcIsGreat
GO
Select * from SQLProcErrorLog

Comments :

0 comments to “SQL 2005+ Store Proc Error Handling”