@@Error not catching error.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • trialproduct2004@yahoo.com
    #1

    @@Error not catching error.

    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.

    Tags: None
    • Erland Sommarskog
      #2
      Re: @@Error not catching error.

      (trialproduct20 04@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@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Erland Sommarskog
          #3
          Re: @@Error not catching error.

          (trialproduct20 04@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@sommarsk og.se

          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

            Comment

            Previous template Next
            Working...