SQL Server - on failure of stored procedure

Asked By Sudeep on 13-Apr-11 03:31 AM
Hi,

my question is....

let us suppose that there is a stored procedure.
if suppose that when the execution of stored procedure fails .
can we capture the error message of that error.

can some one help me please.

Thanks,
raj.
kanika aggarwal replied to Sudeep on 13-Apr-11 03:55 AM
What is your condition for failure of execution

For logic erroryou can use@@ERROR to capture and return the error to your code

http://msdn.microsoft.com/en-us/library/ms188790.aspx
div v replied to Sudeep on 13-Apr-11 03:56 AM
Hi,

A TRY ….CATCH construct includes a TRY block followed by a CATCH block. The TRY block contains a set of transact SQL statements. If an error occurs in any statements of the TRY block, the control is passed to the CATCH block, which contains another set of SQL statements.
 
The CATCH block contains SQL statements that executes only during errors in TRY block. The general syntax of the TRY…CATCH block is as shown below
 
  BEGIN TRY
 
          <SQL Statements…..>
 
          <SQL Statements…..>
 
  END TRY
 
  BEGIN CATCH
 
          <SQL Statements…..>
 
          <SQL Statements…..>
 
  END CATCH
 
 If there are no errors in the try block, the control is passed to the statement after END CATCH statement. In this case, the statements in CATCH block are not executed.
 
The TRY ….CATCH can also be nested. Either the TRY block or the CATCH block can contain the nested TRY ….CATCH construct.
 
In the catch block we generally use the following functions to determine the information about errors.
 
  * ERROR_LINE() : Returns the line number at which error occurred.
  * ERROR_MESSAGE(): Returns the text of the message .  The text includes values , parameters, lengths, object names etc.
  * ERROR_NUMBER() : returns error number
  * ERROR_PROCEDURE():returns the SP or trigger name in which the error occurred. Returns NULL if the error is not in any SP or trigger.
  * ERROR_SEVERITY(): returns the severity of the error.
  * ERROR_STATE(): Returns the state of the error
 
Consider a simple example as shown below. Here the character length of the EmpID field is char(5) and I am trying to insert a string of length 6, this will  result in an error during runtime. I have used the TRY ….CATCH construct to capture this error
 
  BEGIN TRY
 
         Insert into EmpDetails(EmpID,EmpName)
 
         Values (‘EL2345’,’Smith’)
 
  END TRY
 
  BEGIN CATCH
 
         Select  ‘There was an error! ‘ + ERROR_MESSAGE() AS ERRORMESSAGE
 
         ,ERROR_NUMBER()
 
         ,ERROR_LINE()
 
         ,ERROR_PROCEDURE()
 
        ,ERROR_SEVERITY()
 
         ,ERROR_STATE()
 
  END CATCH

Example
in below procedure i did one wrong thing thats @pfirstname as nvarchar but i put wrongly as int
that error has catch by using try catch smt:

CREATE PROCEDURE [dbo].[INSERT_EmpDetails2]
@pFirstName int,
@pLastName Nvarchar(50),
@pDepartment Nvarchar(50)
AS
SET NOCOUNT ON
 
 
 
BEGIN TRY
 
INSERT INTO Emp(EmpName,Designation) VALUES(@pFirstName+'.'+@pLastName,@pDepartment)
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() as ErrorNo,
      ERROR_MESSAGE() as ErrMessage
END CATCH
 
 
SET NOCOUNT OFF

Result:

ErrorNo   ErrMessage
----------- --------------------------------------------------------------------
245     Conversion failed when converting the varchar value '.' to data type int.
Venkat K replied to Sudeep on 13-Apr-11 04:15 AM
Simply add the SELECT @@Error Statement next to the query where you want to capture the error.
Declare @intError INT
Declare @VcDescr VARCHAR(1000)
Insert testtable (PkId ) select null  -- expected error as above
Select @intError =  @@ERROR
If @intError <> 0
Begin
      Exec GetErrorStringSP @intError, @VcDescr OUTPUT
      Print @VcDescr
End

You can also use TRY CATCH blocks if you are using SQL 2005 or above.
Riley K replied to Sudeep on 13-Apr-11 04:22 AM
You can use TRY CATCH to capture errors

 see below example
 
 
BEGIN TRY
   -- This will generate an error, as ProductID is an IDENTITY column
   -- Ergo, we can't specify a value for this column...
   INSERT INTO Products(ProductID, ProductName)
   VALUES(1, 'Test')
END TRY
BEGIN CATCH
   SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH
Sudeep replied to kanika aggarwal on 13-Apr-11 07:40 AM
here the situation is ...

i am transferring data from one table to another table from different places and different databases.
let us suppose that after transferring 50 rows , if an error occurs for next rows then during such condition can we capture such error and enter that error in table in another database.

Thanks for ur help