Missing exceptions in code after Raiserror() and SET NOCOUNT ON workaround

These days I worked on a problem related with using raiserror() in SQL 2000 and why the exception is not raised in ADO.NET application using ODBCCommand. What I had is a simple stored procedure, that performs some check on its parameters and if some of them are incorrect it raises error and exits:

create procedure dbo.TestRaiserrrorinCode (@ClientCode nvarchar(10)) as

 

if not exists (

      select Id

      from dbo.Clients

      where Code = @ClientCode)

begin

      raiserror(58005, 16, 1, @ClientCode)

      return 58005

end

 

print 'Do Something'

 

If you execute it in SSMS you will see:

Msg 58005, Level 16, State 1, Procedure TestRaiserrrorinCode, Line 8

Cannot find client with code '34'.

As you can see, because of the return the print does nothing. So in the SQL it works fine.

But what if you execute this procedure from the C# code using ExecuteNonQuery()? The exception is not raised and the procedure seems to be executed normally. But it is not – there is no exception in the C# code, but the procedure didn’t execute successfully either.

I started my investigation by playing sit SET XACT_ABORT ON, but it didn’t help – it was not related to this situation.

I tried to remove the return after the raiserror() call, but as you might know raiserror() do not stop the execution so I get ‘Do something’ printed, which is not acceptable.

What’s the solution?

I’ve noticed that I have missed the SET NOCOUNT ON and decided to add it. And miraculously it helped. The procedure worked fine, the C# code throws the exception.

How and why the SET NOCOUNT to ON affects this – I do not know. But the fact is that without it the procedure does not behave as it is expected.

Hope that helps you!