The stored procedure does not return the affected rows when INSERT is not executed

I have a stored procedure which is supposed to do an insert but checks first if the primary key is already used by having an IF NOT EXISTS before the insert.

IF NOT EXISTS(SELECT ...)
INSERT INTO ...

So I generate the primary key via code because it is used as a token. I now want to catch the very unlikely case that the generated key already exists in the database by looking at the return value of the SqlCommand.ExecuteNonQuery method which is supposed to be the count of affected rows.

If the insert is executed the method does return '1' as the count of affected rows but if the insert is not executed because of the IF statement the stored procedure does not return a count of affected rows at all. It simply says 'Command(s) completed successfully.' in SQL Server Management Studio and therefore the ExecuteNonQuerymethod returns -1.

Why is this and can I change this behavior somehow or is this just how it works?

---

You probable have NOCOUNT set to ON...

You can test this in your management studio with the following queries:

Example NOCOUNT OFF:

SET NOCOUNT OFF

SELECT *
FROM [dbo].[table1]

Returns (n row(s) affected)

Example NOCOUNT ON:

SET NOCOUNT ON

SELECT *
FROM [dbo].[table1]

Returns Command(s) completed successfully.