ExecuteNonQuery Returns -1 when the code succeeds

Question

I have written a tool in C# that allows my manager to run a set of SQL commands at the end of every month. The SQL commands are stored in a database so I can easily maintain it as requirements change. Each successive command may or may not be allowed to run based on the success of the previous command.

Actually the command stored in my database is just a SQL command that calls a stored procedure where all the complex stuff is.

I'm using the command C# ExecuteNonQuery() and this usually returns a -1 if it fails and something else if it succeeds (so far so good). It has been working like this for years.

Now comes the maintenance bit.

All of my stored procedures have previously been updating data in my database. I now need to add a stored procedure that checks if something already exists in the database. If it doesn't exist or fails completely I want to return -1 or something else if the data does exist.

I have tried turning SET NOCOUNT OFF and that didn't help and I think I have narrowed it down the the fact that my latest procedure doesn't update anything and therefore ExecuteNonQuery sees this as a failure.

I have run the commands in Management Studio and they report success as expected. The current command even returns "1 row(s) effected" in the messages tab.

Here is the stored procedure code so you can see what I have tried so far. I'm trying to avoid having to modify my C# program so that uses different commands depending on the type of SQL called.

Does anyone have ideas?

USE [Billing]
GO
/****** Object:  StoredProcedure [dbo].[Billing_Test_Uncharged_Entries]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 
ALTER PROCEDURE [dbo].[Billing_Test_Uncharged_Bing_Entries] 
    -- Add the parameters for the stored procedure here
AS

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

BEGIN TRY
    --SET NOCOUNT ON;
    Set NOCOUNT OFF;

    -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    If Not Exists
    (
        SELECT [lSundriesID]
        FROM [Strata].[dbo].[vjrSundries]
        where 
        lFeeCodeID = '77' And
        -- lFeeCodeID = '999' And  --(Use this line to force an error during testing)
        lMonthfeeApplies is null
    )
    Begin
        --print 'Nothing to see here'
        --Return -1

        SELECT 
            @ErrorMessage = 'No FeeCodeID=77 Items found, Has the Bing EP Mailroom import process been run in Sundries',
            @ErrorSeverity = 11,
            @ErrorState = 1;

            RAISERROR (
                @ErrorMessage,  -- Message text
                @ErrorSeverity, -- Severity
                @ErrorState     -- State
            );
    End
    Else
    Begin
        Select 'Found Something'
    End
END Try
Begin Catch

            SELECT 
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

                RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                @ErrorState -- State.
                );
End Catch

Answer 1

ExecuteNonQuery returns the number of rows affected by a DELETE, INSERT or UPDATE statement - because that's what it's used for typically. It does NOT return the number of rows selected from a SELECT statement - instead it returns -1.

BUT: since the ExecuteNonQuery call doesn't give you a chance to actually get the data returned, it really doesn't make much sense with a SELECT - you should use an ExecuteReader call instead, which returns a SqlDataReader which you can then use to iterate over the data returned by the SELECT statement and use that data in whichever way you need it.

Answer 2

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers.

For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

if we run stored procedure via ExecuteNonQuery it will be returned -1 always

see this page also.