Exception Handling using Try-Catch in SQL Server 2005
Posted by Jugal Shah on August 27, 2008
/*
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;
Ashish Jain said
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?
JShah said
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