CODEWITHSUNDEEP
stored-procedureserror-handlingsql-server-2012try-catch
Matt
Matt

Reputation: 11

Try/Catch fails to catch exception in stored procedure

I'm struggling to get my try/catch to work with my stored procedure, it seems to not catch the error. I've been searching around, but no luck. What I'm trying to accomplish is to display the error number if an invalid value, no value, or null is given when the stored procedure is called. I've tried moving the try and catch it feels like everywhere, but it seems like the error may be on the "@SomeID as int" line. I appreciate any help I can get.

alter procedure DeleteAcct
@SomeID as int
as
begin try
declare @counter int
set @counter = (select count(SomeDate) from Table1 where SomeID = @SomeID group by SomeID)
begin
	if(@counter > 0)
	begin
	Print 'Testing'
	end
	else 
	begin	
	delete from Table2
	where SomeID = @SomeID
	Print 'Delete successful'
	end
end
end try
begin catch
	print error_number()
end catch

<!-- calling the stored procedure -->
exec DeleteAcct '1231231231221'

<!-- Error received -->
Msg 8114, Level 16, State 5, Procedure DeleteAcct, Line 0
Error converting data type varchar to int.

Upvotes: 1

Views: 690

Answers (2)

davidc2p
davidc2p

Reputation: 320

Well this answer is late, but we do this at work all the time. You should try/catch the launcher and not the SP that is being executed.

I believe the explanation was quite obvious, but lets develop:

  1. The called procedure does not need any try/catch:

    alter procedure DeleteAcct
    @SomeID as int
    as
    declare @counter int
    set @counter = (select count(SomeDate) from Table1 where SomeID = @SomeID group by SomeID)
    begin
        if(@counter > 0)
        begin
        Print 'Testing'
        end
        else 
        begin   
        delete from Table2
        where SomeID = @SomeID
        Print 'Delete successful'
        end
    end
  1. The launcher or the calling procedure needs to be put into try/catch block so that the error from the executed procedure is displayed at the launcher level.

    begin try
        exec DeleteAcct '1231231231221'
    end try
    begin catch
        print error_number()
    end catch

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

You declare param @SomeID as an int then try to pass a string!

Make call like so:

exec DeleteAcct 1231231231221

In fact I suspect @SomeID should be declared as a varchar

Upvotes: 2

Related Questions