471,108 Members | 1,334 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Trapping Errors in Stored Procedure

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
Jul 20 '05 #1
2 7608
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
Jul 20 '05 #2
[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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Walter | last post: by
1 post views Thread by DB_2 | last post: by
6 posts views Thread by Peter Frost | last post: by
13 posts views Thread by Thelma Lubkin | last post: by
2 posts views Thread by Captain Nemo | last post: by
9 posts views Thread by 47computers | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.