sql server - ExecuteNonQuery always returns -1 -

i have created stored procedure deleting record. in stored procedure first checking usage of data going delete. if being used, stored procedure return -2 otherwise deletes record.

but problem record exists return -1 instead of -2. have set nocount off don't know problem.

i know question answered setting nocount off not working me

alter procedure [dbo].[spdeletepidnumber]     @id int begin     set nocount off;      -- insert statements procedure here     if(exists(select * tblbills pid = @id))     begin         return -2     end     else     begin         delete helperpidnumber          id = @id     end end  public int deletepidnumber(int id) {         try         {             int result = 0;              using (sqlconnection conn = new sqlconnection(properties.settings.default.connection))             {                 var cmd = new sqlcommand("spdeletepidnumber", conn);                 cmd.commandtype = system.data.commandtype.storedprocedure;                 cmd.parameters.addwithvalue("@id", id);                  conn.open();                 result = cmd.executenonquery();             }              return result;         }         catch         {             throw;         }     } 

from executenonquery documentation:

executes transact-sql statement against connection and returns number of rows affected.

having set nocount on; in procedure explicitely tells sql server not return row count. in case return of executenonquery function -1.

also if procedure not affect rows, not return row count either if nocount off. in case return -1.

---

what appear want return value of stored procedure. not result of executenonquery. please refer question on stackoverflow: getting return value stored procedure in ado.net