How to catch an exception?

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!>
> 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|||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!|||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/techin.../2000/books.asp|||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/techin.../2000/books.asp