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

Health Care Costs Blows

I think Health care cost can be improved if these monkeys(gov,ipa's,any one that bills a claim) would just sit down as revisit their oldest technologies and work flows.