Showing posts with label handling. Show all posts
Showing posts with label handling. Show all posts

Monday, March 19, 2012

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