Is there a way to manually set @@ROWCOUNT?

Question

Consider this script to illustrate what I want:

SET NOCOUNT OFF
DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
INSERT INTO @table(col2) VALUES (1),(2),(3),(4)

This will show (4 row(s) affected)

Now what I want:

SET NOCOUNT ON
DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
INSERT INTO @table(col2) VALUES (1),(2),(3),(4)
-- do other stuff...

SET NOCOUNT OFF
SELECT @@ROWCOUNT = 666 -- return this value to client with ExecuteNonQuery()

Obviously SELECT @@ROWCOUNT = 666 is incorrect syntax.

I need to set @@ROWCOUNT manually, and return that value to a c# client with rowsAffected = ExecuteNonQuery(...)

Can this be done?
(Note: I use a stored procedure, and do not want to use an OUT parameter or return a recordset)

Answer 1

The obvious way to create an artificial (rows affected) message is to perform an action that affects that number of rows whilst having as few side effects as possible:

declare @t table (n int not null)
;With Numbers (n) as (
    select ROW_NUMBER() OVER (ORDER BY so1.object_id)
    from sys.objects so1,sys.objects so2
)
insert into @t(n) select n from Numbers where n<=666

Whether it will be sufficient to trick ExecuteNonQuery I couldn't say. (If you have an actual Numbers table you can use that in place of the CTE, though you may have to adjust the filtering if it contains 0 or negative numbers)

Answer 2

Another way to achieve this is:

SET NOCOUNT ON
-- Do stuff
SET NOCOUNT OFF
-- Run the actual query that will affect the specified number of rows
SET NOCOUNT ON
-- Do more stuff

Example:

CREATE PROCEDURE Example1
AS
SET NOCOUNT ON
    DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)
    INSERT INTO @table(col2) VALUES (1),(2),(3),(4)
SET NOCOUNT OFF
    SELECT a.*
    INTO #NoWhere
    FROM @table AS a
        CROSS JOIN @table AS b
SET NOCOUNT ON
    SELECT COUNT(*)
    FROM @table AS a
        CROSS JOIN @table AS b
GO;
EXEC Example1
-- (16 row(s) affected)