locked
How to capture stored pro. error RRS feed

  • Question

  • User-804681621 posted

    I have created stored procedure and select some days from the view.<br>
    <br>
    But when I indiviual just select * from the view into temptable, it return error about 'decide by zero error encountered'<br>
    <br>
    But when I exec the stored procedure, it will not capture error.<br>
    <br>
    How can I capture the error from stored procedure?<br>
    <br>
    Monday, March 7, 2016 10:00 AM

All replies

  • User1578460427 posted

    Use a TRY...CATCH block in the stored procedure. The latter link contains an example of a stored procedure that catches a divide by zero error using a TRY...CATCH block.

    Monday, March 7, 2016 10:43 AM
  • User-595703101 posted

    Hi pn_nq,

    You can check how SQL TRY CATCH can be implemented on your case in below sample code

    If you execute the SQL stored procedure a few times, it will throw an error with divide by zero case

    create procedure SP_DivideByZero
    as
    
    declare @i float
    select @i = RAND() * 2
    
    BEGIN TRY
         select 1 / floor(@i)
    END TRY
    BEGIN CATCH
    SELECT
    	@i,
    	ERROR_NUMBER() AS ErrorNumber,
    	ERROR_SEVERITY() AS ErrorSeverity,
    	ERROR_STATE() AS ErrorState,
    	ERROR_PROCEDURE() AS ErrorProcedure,
    	ERROR_LINE() AS ErrorLine,
    	ERROR_MESSAGE() AS ErrorMessage
    END CATCH
    GO
    
    exec SP_DivideByZero

    Monday, March 7, 2016 1:11 PM
  • User-804681621 posted
    I already used try....catch, but it run successfully.

    But I try to execute the view which join another table, It also run successfully. But when I select * from the view, it return divide by zero error.

    Why? How can I capture error after joining table?
    Monday, March 7, 2016 3:05 PM
  • User-510141866 posted

    Can you post your view script here?

    Monday, March 7, 2016 3:16 PM
  • User-804681621 posted

    Select (amount/tryamt ) * 6 from table , where some data for tryamt is zero  <--- So it causes divide zero error

    Select (a.amount/a.tryamt ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id  <-- it will not throw error after joining another table, why?

    Monday, March 7, 2016 3:28 PM
  • User-595703101 posted

    Probably by INNER JOIN the rows with "tryamt" field as 0 are eliminated from the result set

    For example if you use LEFT JOIN instead of INNER JOIN, the rows containing problematic data will be in the result set. And this will throw error.

    Monday, March 7, 2016 5:50 PM
  • User-510141866 posted

    Select (amount/tryamt ) * 6 from table

    Select CASE WHEN  tryamt =0 TEHN 0 ELSE (amount/tryamt ) * 6 END AS 'RESULT' from table

    TRY LIKE THIS

    Monday, March 7, 2016 5:57 PM
  • User-804681621 posted
    But my case is the inner join, how can I capture error if inner join.
    Tuesday, March 8, 2016 2:00 AM
  • User-219423983 posted

    Hi pn_nq,

    According to the following link you could know how to avoid this error by using nullif or case…when expression as aspsak mentioned.

    http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql

    So, you’d better be careful with the value of “tryamt” and then use the following code to achieve your goal.

    Select (amount/nullif(tryamt,0) ) * 6 from table
    Select (a.amount/nullif(a.tryamt,0) ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id

    Besides, you could have a look at the following link that’s about the NULL in SQL operations.

    http://www.firebirdsql.org/manual/nullguide-oper.html

    About capturing errors in stored procedure, you could also refer to the following links to get it by using RAISERROR.

    http://www.sqlteam.com/article/handling-errors-in-stored-procedures

    https://support.microsoft.com/en-us/kb/321903

    Best Regards,

    Weibo Zhang

    Tuesday, March 8, 2016 4:24 AM
  • User-804681621 posted

    Hi pn_nq,

    According to the following link you could know how to avoid this error by using nullif or case…when expression as aspsak mentioned.

    http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql

    So, you’d better be careful with the value of “tryamt” and then use the following code to achieve your goal.

    Select (amount/nullif(tryamt,0) ) * 6 from table
    Select (a.amount/nullif(a.tryamt,0) ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id

    Besides, you could have a look at the following link that’s about the NULL in SQL operations.

    http://www.firebirdsql.org/manual/nullguide-oper.html

    About capturing errors in stored procedure, you could also refer to the following links to get it by using RAISERROR.

    http://www.sqlteam.com/article/handling-errors-in-stored-procedures

    https://support.microsoft.com/en-us/kb/321903

    Best Regards,

    Weibo Zhang

    sorry, my purpose is not avoided the error. i just want to throw the error when tryamt is zero. now after inner join, the view does not throw error.

    how can i catch the error when tryamt is zero from below sql

    create procedure sp_test
    as
    begin
    Select (a.amount/ tryamt) ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id
    end

    how to catch error when run stored procedure??

    Tuesday, March 8, 2016 4:43 PM
  • User753101303 posted

    Hi,<br>
    <br>
    What if you try to add SET NOCOUNT ON at the top of your SP if you don't have that already?<br>
    <br>
    It's been a while but if I remember it came into play for reporting some errors (not sure about this particular one, would have to try). Your SP have multiple statement and does somthing such as inserting data into a temp table before doing other thing and maybe ultimately returning a result set ?
    Tuesday, March 8, 2016 7:18 PM
  • User-510141866 posted

    pn_nq

    create procedure sp_test
    as
    begin
    Select (a.amount/ tryamt) ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id
    end

    create procedure sp_test
    @msg varchar (500) output
    as
    begin
    try
      begin
       Select (a.amount/ tryamt) ) * 6 as amt, b.name from table a inner join tableB b on a.id=b.id
      end
    catch
     begin
        set @msg = (select ERROR_MESSAGE())
     end


    https://msdn.microsoft.com/en-IN/library/ms190358.aspx

    Wednesday, March 9, 2016 3:25 PM