How to catch an exception?

[Evgeny Gopengauz]

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!

[Garry]

>

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

[Evgeny Gopengauz]

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!

[Erland Sommarskog]

Evgeny Gopengauz (ev***@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, es****@sommarskog.se

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

[Erland Sommarskog]

Garry (va***@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, es****@sommarskog.se

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