We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.
UPDATE PERSONS
SET FLOORNR = dbo.FloorNR(OFFICELOCATION)
WHERE OFFICELOCATION IS NOT NULL
IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT = 0)
BEGIN
RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait
END
However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.
How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.
Is it possible for you to try to handle some of those situations inside of the function and have it not return any values in the case of an error?|||We already handle a number of situations inside the functions. But once every week a user finds a new way to enter his office location and our procedure stops responding. At the moment, we have no way of controlling the input by our users, but we don't want to keep on expanding the procedure. In fact, we just want users to enter their office location in a standard way. We will change the application which handles that, but that change is still some months away. Untill then, we just want the catch any errors and log them.
No comments:
Post a Comment