Trapping Errors in Stored Procedure

[Brian]

Hi there,

I am converting a large PL/SQL project into Transact-SQL and have hit
an issue as follows:

I have a PL/SQL procedure that converts a string to a date. The
procedure does not know the format of the date in the string so it
tries loads of formats in converting the string to a date until it
succeeds.

After trying each potential format it uses the Oracle 'EXCEPTION WHEN
OTHERS' construct to trap the failure so it can try another format.

Is it possible to do this with SQLServer ? If I do a CONVERT and it is
not one of the standard formats it fails. This is part of a background
scheduled process and I cannot afford the procedure to bomb out.

I suspect the answer is I cannot do this and will need to impose some
control over the string being received (from various external
systems!!) to ensure it is a specific known format. Even if I know it
will be one of the known SQLServer formats this will not be enough
since if the first one I try is not correct the process will crash.

Any ideas ?

Thanks

[Dave Michaud]

br***********@infotechnics.co.uk (Brian) wrote in message news:<17**************************@posting.google. com>...

Hi there,

I am converting a large PL/SQL project into Transact-SQL and have hit
an issue as follows:

I have a PL/SQL procedure that converts a string to a date. The
procedure does not know the format of the date in the string so it
tries loads of formats in converting the string to a date until it
succeeds.

After trying each potential format it uses the Oracle 'EXCEPTION WHEN
OTHERS' construct to trap the failure so it can try another format.

Is it possible to do this with SQLServer ? If I do a CONVERT and it is
not one of the standard formats it fails. This is part of a background
scheduled process and I cannot afford the procedure to bomb out.

I suspect the answer is I cannot do this and will need to impose some
control over the string being received (from various external
systems!!) to ensure it is a specific known format. Even if I know it
will be one of the known SQLServer formats this will not be enough
since if the first one I try is not correct the process will crash.

Any ideas ?

Thanks

For the most part T-SQL doesn't stop for errors. If you want to trap
errors you need to periodically check the value of @@ERROR. Here's an
example from Microsoft's books online:
-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO

Dave

[Erland Sommarskog]

[posted and mailed, please reply in news]

Brian (br***********@infotechnics.co.uk) writes:

I am converting a large PL/SQL project into Transact-SQL and have hit
an issue as follows:

I have a PL/SQL procedure that converts a string to a date. The
procedure does not know the format of the date in the string so it
tries loads of formats in converting the string to a date until it
succeeds.

After trying each potential format it uses the Oracle 'EXCEPTION WHEN
OTHERS' construct to trap the failure so it can try another format.

Is it possible to do this with SQLServer ? If I do a CONVERT and it is
not one of the standard formats it fails. This is part of a background
scheduled process and I cannot afford the procedure to bomb out.

I suspect the answer is I cannot do this and will need to impose some
control over the string being received (from various external
systems!!) to ensure it is a specific known format. Even if I know it
will be one of the known SQLServer formats this will not be enough
since if the first one I try is not correct the process will crash.

SQL Server's error handling is nothing to write home about. Indeed,
syntax error in dates often causes batch-termination.

I have written two articles on error handling in SQL Server. You might
want to start with the second of them:
http://www.algonet.se/~sommar/error-handling-II.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp