Hi all,

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.

Say suppose i have statment like

select * from emp where rowid = 'a'
PRINT @@ERROR
print 'reach'

here rowid is integer value so i am getting above mention error.

So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

thanks in advance.

(trialproduct2004@yahoo.com) writes:
> I want to catch error in stored procedure and return error message.
> I want to catch error 'Syntax error converting the varchar value 'a'
> to a column of data type int.' Means error occuring if i enter wrong
> value.
>
> Say suppose i have statment like
>
> select * from emp where rowid = 'a'
> PRINT @@ERROR
> print 'reach'
>
> here rowid is integer value so i am getting above mention error.
>
> So what i am expecting is it should print error and then print 'reach'
> which is not happening.
> can anyone tell me reason behind this and how to overcome this
> problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

(trialproduct2004@yahoo.com) writes:
> I want to catch error in stored procedure and return error message.
> I want to catch error 'Syntax error converting the varchar value 'a'
> to a column of data type int.' Means error occuring if i enter wrong
> value.
>
> Say suppose i have statment like
>
> select * from emp where rowid = 'a'
> PRINT @@ERROR
> print 'reach'
>
> here rowid is integer value so i am getting above mention error.
>
> So what i am expecting is it should print error and then print 'reach'
> which is not happening.
> can anyone tell me reason behind this and how to overcome this
> problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx