--// 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
How To Create An Engaging And Useful Chatbot
3 hours ago