Exception Handling using Try-Catch in SQL Server 2005


/*
Error Handling using Try Catch Block
*/

CREATE PROCEDURE usp_GetErrorInfo
AS
BEGIN
SET NOCOUNT ON
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
END
GO

/*
TRY – CATCH in user defined stored procedure
*/

ALTER PROCEDURE TEST
AS
BEGIN
BEGIN TRY
— Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
— Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH
END
GO

EXECUTE TEST
GO;

Advertisements

2 thoughts on “Exception Handling using Try-Catch in SQL Server 2005

  1. Ashish Jain

    Q.How to call user defined function through procedure?
    funciotn is :
    ALTER FUNCTION [dbo].[JobStatusAllInfo]
    (
    — Add the parameters for the function here
    @JOBNAME varchar(50)
    )
    RETURNS Table
    AS
    RETURN
    SELECT step_id,message,run_status from sysjobhistory WHERE sysjobhistory.job_id =
    (SELECT sysjobs.job_id from sysjobs WHERE name=@JOBNAME)

    Q. How to store funtion output in text file?

    Reply
    1. JShah Post author

      The above function is called “IN LINE TABLE VALUED FUNCTION”

      You can call it in SP by writing simple select statement (” SELECT * FROM JobStatusAllInfo(“JOBNAME”)

      Now store output in text file you can use BCP command/utility

      Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s