Try..Catch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Try..Catch

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-01-22 : 16:08:01

CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;

Here I have to insert those Error records into a error_table. I need your help to do this. Can I use Into clause? If so how?

Thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-22 : 16:28:53

You could do it this way:

CREATE PROCEDURE usp_GetErrorInfo (
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(126),
@ErrorLine INT,
@ErrorMessage NVARCHAR(2048) )
AS

INSERT YourErrorTable ( Columns... )
VALUES ( @ErrorNumber, @ErrorSeverity,
         @ErrorState,  @ErrorProcedure,
         @ErrorLine,   @ErrorMessage )
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
                         ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
END CATCH;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources