Error trapping in a - 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 2000 Forums

SQL Server Development (2000)

Error trapping in a

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-15 : 08:36:28

John writes "I want to trap an error in a User defined Function. I have deliberately introduced an error in the function but I am unable to trap it. Please have a look at the code below, the error I intoduced and what the Server Message says.

I also would like to store the Server Message somewhere but how do I retrieve the Message if I don't know which Error the code may encounter?

May thanks for your help.

John

ALTER FUNCTION pims.Get_All_Alerts (@p_patnt_refno int)
RETURNS VARCHAR(80) AS

/*
SQLs to test the function:
1) Standalone:
SELECT pims.Get_All_Alerts(1607030) AS Alerts

2) As part of an SQL Statement:

SELECT
pasid PASID,
pims.Get_All_Alerts(patnt_refno) Alerts
FROM patients WHERE pasid = 'GVT5317'
*/

BEGIN

-- Deckare Variables
DECLARE @v_description VARCHAR(80)
DECLARE @counter INT
DECLARE @v_all_alerts VARCHAR(2000)
DECLARE @v_result VARCHAR(80)
DECLARE @v_resultN NUMERIC
DECLARE @Error NUMERIC

-- Assign values to Variables where appropriate
select @v_all_alerts = ''
select @counter = 1

-- Declare the Cursor
DECLARE c_alerts CURSOR FOR
SELECT oc.description
FROM DIAGNOSIS_PROCEDURES dp, ODPCD_CODES oc
WHERE dp.sorce_refno = @p_patnt_refno
AND dp.odpcd_refno = oc.odpcd_refno
AND dp.dptyp_code = 'ALERT'
AND dp.sorce_code = 'PATNT'
AND dp.cancel_dttm IS NULL
AND dp.end_dttm IS NULL
AND oc.archv_flag = 'N'

-- Open the Cursor
OPEN c_alerts

-- Perform the first fetch
FETCH FROM c_alerts INTO
@v_description
WHILE @@FETCH_STATUS = 0 -- Keep fetching untill the Status changes

BEGIN
IF @counter = 1
BEGIN
SET @v_all_alerts = @v_description
END
ELSE
BEGIN
SET @v_all_alerts = @v_all_alerts + '; ' + @v_description
END

SET @counter = @counter + 1 -- Add to the Counter

-- Perform the next fetch
FETCH NEXT FROM c_alerts INTO
@v_description

END

-- Tidy up and close and deallocate the Cursor
CLOSE c_alerts
DEALLOCATE c_alerts

-- Format the result of the Function if it is too long
IF LEN(@v_all_alerts) >= 80
BEGIN

/************************************************************
Below I am deliberately introducing an error by assigning a string value
(@v_all_alerts) to a numerice variable (@v_resultN).

When running the function then I get the message:

Server: Msg 8115, Level 16, State 6, Procedure Get_All_Alerts, Line 88
Arithmetic overflow error converting varchar to data type numeric.
*/

SELECT @v_resultN = SUBSTRING(@v_all_alerts,1,77) + '...'
SELECT @Error = @@Error
If @ERROR <> 0
BEGIN
SELECT @v_result = 'Error Test 1'
END
END
ELSE
BEGIN
SELECT @v_resultN = @v_all_alerts
SELECT @Error = @@Error
If @ERROR <> 0
BEGIN
SELECT @v_result = 'Error Test 2'
END
END

RETURN ( @v_result )

END"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources