Override rows number affected returned by stored procedure that uses cursor inside

Question

I have a stored procedure simplified to the following code:

SET NOCOUNT ON

DECLARE @count INT
DECLARE @temp TABLE
INSERT INTO @temp
SELECT @count = count(*) FROM @temp

DECLARE [cursor] FOR SELECT id FROM @temp
OPEN [cursor] FETCH NEXT FROM [cursor] INTO @id WHILE @@fetch_status = 0
BEGIN
    exec anoterStoredProc @id
FETCH NEXT FROM [cursor] INTO @id
END
CLOSE [cursor]
DEALLOCATE [cursor]

SET NOCOUNT OFF

RETURN @count

But ExecuteNonQuery() still returns -1!

I can use select @count and ExecuteScalar() but want understand why my code doesn't work.

Seems that NOCOUNT OFF doesn't reset the server behavior, and NOCOUNT ON is still in-place.

score 3 · Accepted Answer · answered May 08 '12 at 17:35

3

First, don't mess with SET ROWCOUNT - that does something very dangerous (if used incorrectly).

Return values are actually handled by adding a parameter with "direction" of ReturnValue. Alternatively, SELECT the value, and use ExecuteScalar.

answered May 08 '12 at 17:35

Marc Gravell

1,026,079
266
2,566
2,900

So either ExecuteScalar(), or Direction=Return and reader it's value, not what has been returned by ExecuteNonQuery(), right – abatishchev May 08 '12 at 18:30
@abatishchev nothing is *returned* from `ExecuteNonQuery` - however, any out/ref/return-value parameters will be updated in-situ, so after the call you can obtain the `.Value` from that parameter – Marc Gravell May 08 '12 at 19:24

Philip Kelley · Answer 2 · 2012-05-08T17:48:02.867

SET ROWCOUNT xx is used to limit the number of rows returned by all subsequent queries. It would not be used to return a scalar value.

RETURN xx is used to return an integer value to the process calling the procedure. Generally this is used to indicate success (zero) or failure (not zero, presumably the error number). For specific values, you might find output parameters more convenient.

[added] SET NOCOUNT is an oblique command. When off, information is returned for every query run (select, update, insert, delete); when on, it is not. (Thus, setting it off where you do would have no effect.) The thing is, this inforation isn't a return value, parameter value, or dataset, but something else again (and is it a number? a string? I'm not sure). From everything I've ever heare it's a pain to work with, which is why I always start my stored procedures with SET NOCOUNT on.

In both cases, I don't seen where @count is being defined or assigned a value.

Will `return x` affect a value returned by `ExecuteNonQuery()`? — abatishchev, May 08 '12 at 17:38
I'm not familiar enough with ado.net to answer that. I'd go with @Marc Gravell's suggestiong (+1) of using a parameter with "ReturnValue". And I'm updating my answer to mention SET NOCOUNT. — Philip Kelley, May 08 '12 at 17:45

Override rows number affected returned by stored procedure that uses cursor inside

2 Answers2