/*
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;
Q.How to call user defined function through procedure?
ReplyDeletefunciotn 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?
The above function is called "IN LINE TABLE VALUED FUNCTION"
ReplyDeleteYou 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