SEO

#1 (**permalink**) 02-29-2008, 03:55 AM

Is there something like exception handling in T-SQL?
For example, how to catch an error of convertion at this
sample:

CREATE PROCEDURE SP
@param VARCHAR(50)
AS BEGIN
DELCARE @var INT
-- try {
SET @var = CONVERT( int, @param)
-- } catch (error#245) {
-- handle an error right here
-- }
END

It must be invisible for a caller of SP if something wrong inside SP.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#2 (**permalink**) 02-29-2008, 03:55 AM

>
> CREATE PROCEDURE SP
> @param VARCHAR(50)
> AS BEGIN
> DELCARE @var INT
> -- try {
> SET @var = CONVERT( int, @param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
>
In this situation you can use ISNUMERIC function.
In T-SQL there are not try..catch constructions and all errors you will get
on client

.

ALTER PROCEDURE SP
@param VARCHAR(50)
AS BEGIN
DECLARE @var INT
-- try {
if ISNUMERIC(@param) = 0
begin
RAISERROR('Error converting @param -> @var',16,10)
RETURN -1
end
SET @var = CONVERT( int, @param)
END
go
exec SP
@param = '1a'
go

#3 (**permalink**) 02-29-2008, 03:55 AM

Hi, Garry!

Thank you for your answer but my question was not about how to suppress
exactly convertion error. I'm looking for something like try-catch. Is
it truth that no way to handle an exception inside the server execution?
It is sad...

Ok, my problem is that: some of my procedures are able to generate both
correct rowset and some error messages at the same time. But when I try
to open the query with EXEC thru OLE DB I receive an error, not rowset

The best issue for me: if I would be able to handle all the errors
inside the stored procedure body...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#4 (**permalink**) 02-29-2008, 03:56 AM

Evgeny Gopengauz (evgop@ucs.ru) writes:
> Is there something like exception handling in T-SQL?
> For example, how to catch an error of convertion at this
> sample:
>
> CREATE PROCEDURE SP
> @param VARCHAR(50)
> AS BEGIN
> DELCARE @var INT
> -- try {
> SET @var = CONVERT( int, @param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
>
> It must be invisible for a caller of SP if something wrong inside SP.

For SQL2000 the answer is very distinctively: NO. Error handling in
SQL Server 2000 is a mess. There are two articles on my web site about
the topic http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html.

The good news is that in the next version of SQL Server, SQL 2005 which
now is in beta, there are great improvements in this area, and there
is indeed a TRY-CATCH construct.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

#5 (**permalink**) 02-29-2008, 03:56 AM

Garry (vadim@viii.ntu-kpi.kiev.ua) writes:
> In this situation you can use ISNUMERIC function.

No, you can never use the isnumeric() function, because it is
virtually useless. isnumeric() tells you that a string can be
converted to some numeric data type, but you can find out which. A
string that can be converted to money may not convert to float or
vice versa.

For test of a positive integer number, this is the way to do:

@x NOT LIKE '%[^0-9]%'

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to catch an exception?