T-SQL Return row affected count in CLR proc?

Is there a way to say to the caller just like an Insert/update/delete
command would do from a T-SQL CLR Stored Procedure how many rows my
procedure affected? I know I can push text down the SqlContext's Pipe as
messages but how do you say here is my rows affected count? thanks!

Niels
Fri Feb 01 11:33:46 CST 2008

Smokey Grindel wrote:
> Is there a way to say to the caller just like an Insert/update/delete
> command would do from a T-SQL CLR Stored Procedure how many rows my
> procedure affected? I know I can push text down the SqlContext's Pipe as
> messages but how do you say here is my rows affected count? thanks!
>
>
If you are updating/inserting/deleting data from a CLR based proc (which
you shouldn't do unless you really have to), let the proc's return value
be the number of rows affected.

Niels

Smokey
Fri Feb 01 11:45:13 CST 2008

my problem is it doesnt return a value... the proc is very complex which is
why we have it this way... but i need to say yes it did update these rows...
and it is not saying anything when it executes

"Niels Berglund" <nielsb@nospam.develop.com> wrote in message
news:eoS3YiPZIHA.5028@TK2MSFTNGP04.phx.gbl...
> Smokey Grindel wrote:
>> Is there a way to say to the caller just like an Insert/update/delete
>> command would do from a T-SQL CLR Stored Procedure how many rows my
>> procedure affected? I know I can push text down the SqlContext's Pipe as
>> messages but how do you say here is my rows affected count? thanks!
> If you are updating/inserting/deleting data from a CLR based proc (which
> you shouldn't do unless you really have to), let the proc's return value
> be the number of rows affected.
>
> Niels

Niels
Fri Feb 01 12:10:19 CST 2008

Smokey Grindel wrote:
> my problem is it doesnt return a value... the proc is very complex which is
> why we have it this way... but i need to say yes it did update these rows...
> and it is not saying anything when it executes

I assume you call SqlCommand.ExecuteNonQuery from your SQLCLR code, to
execute the necessary insert/update/delete statement's? If so,
ExecuteNonQuery reports the number of rows affected as return value of
the code.

Niels

Smokey
Fri Feb 01 12:22:14 CST 2008

Yes its executed using the executenonquery but it always returns zero... and
SET NOCOUNT is set to off

"Niels Berglund" <nielsb@nospam.develop.com> wrote in message
news:%23Muf02PZIHA.5164@TK2MSFTNGP03.phx.gbl...
> Smokey Grindel wrote:
>> my problem is it doesnt return a value... the proc is very complex which
>> is why we have it this way... but i need to say yes it did update these
>> rows... and it is not saying anything when it executes
>
> I assume you call SqlCommand.ExecuteNonQuery from your SQLCLR code, to
> execute the necessary insert/update/delete statement's? If so,
> ExecuteNonQuery reports the number of rows affected as return value of the
> code.
>
> Niels

Niels
Fri Feb 01 12:34:20 CST 2008

Smokey Grindel wrote:
> Yes its executed using the executenonquery but it always returns zero... and
> SET NOCOUNT is set to off
>

Hmm, weird. When you execute from your SQLCLR code, are you executing a
SQL Statement, or are you calling into a T-SQL proc? If you are
executing against a SQL statement, you could always as last part of the
statement do a "SELECT @@rowcount;" and instead of ExecuteNonQuery, do a
ExecuteScalar. That'd give you the row-count back. If you are executing
against a T-SQL proc, would it be possible to change the proc to return
the row-count?

Niels

Smokey
Fri Feb 01 12:53:55 CST 2008

There is a lot of logic in there that is not just SELECT statements and
INSERTS... the insert and selects are of course inside sqlcommand objects
running on the context=true database connection... i load data alter it
based on business logic then insert results into another table... all
wrapped inside a sqltransaction that is committed at the end when all the
inserts are done... each row is inserted independently... but when the
transaction completes the row count altered is zero


"Niels Berglund" <nielsb@nospam.develop.com> wrote in message
news:eZgcREQZIHA.4324@TK2MSFTNGP03.phx.gbl...
> Smokey Grindel wrote:
>> Yes its executed using the executenonquery but it always returns zero...
>> and SET NOCOUNT is set to off
>>
>
> Hmm, weird. When you execute from your SQLCLR code, are you executing a
> SQL Statement, or are you calling into a T-SQL proc? If you are executing
> against a SQL statement, you could always as last part of the statement do
> a "SELECT @@rowcount;" and instead of ExecuteNonQuery, do a ExecuteScalar.
> That'd give you the row-count back. If you are executing against a T-SQL
> proc, would it be possible to change the proc to return the row-count?
>
> Niels

Niels
Fri Feb 01 15:46:03 CST 2008

Smokey Grindel wrote:
> There is a lot of logic in there that is not just SELECT statements and
> INSERTS... the insert and selects are of course inside sqlcommand objects
> running on the context=true database connection... i load data alter it
> based on business logic then insert results into another table... all
> wrapped inside a sqltransaction that is committed at the end when all the
> inserts are done... each row is inserted independently... but when the
> transaction completes the row count altered is zero

Well, so - in your SQLCLR method that is doing a load of T-SQL work (as
per above), you need in business logic define what the count of affected
rows are. I.e, if you do some inserts, some deletes and some updates -
how do you define row-count? After having defined that, you should be
able to return whatever it is. What I'm getting at is that each of your
T-SQL statements will produce a rowcount, it is up to you to collect
that information and do something about it.

Niels

Smokey
Fri Feb 01 18:42:04 CST 2008

Yeah, I am already collecting it and have a total row changed count, just
didnt know if i could force it down as a row changed count or if i will need
to pass it back as a output param or something else

"Niels Berglund" <nielsb@nospam.develop.com> wrote in message
news:Oi2fZvRZIHA.1132@TK2MSFTNGP06.phx.gbl...
> Smokey Grindel wrote:
>> There is a lot of logic in there that is not just SELECT statements and
>> INSERTS... the insert and selects are of course inside sqlcommand objects
>> running on the context=true database connection... i load data alter it
>> based on business logic then insert results into another table... all
>> wrapped inside a sqltransaction that is committed at the end when all the
>> inserts are done... each row is inserted independently... but when the
>> transaction completes the row count altered is zero
>
> Well, so - in your SQLCLR method that is doing a load of T-SQL work (as
> per above), you need in business logic define what the count of affected
> rows are. I.e, if you do some inserts, some deletes and some updates - how
> do you define row-count? After having defined that, you should be able to
> return whatever it is. What I'm getting at is that each of your T-SQL
> statements will produce a rowcount, it is up to you to collect that
> information and do something about it.
>
> Niels

Niels
Sat Feb 02 05:42:48 CST 2008

Smokey Grindel wrote:
> Yeah, I am already collecting it and have a total row changed count, just
> didnt know if i could force it down as a row changed count or if i will need
> to pass it back as a output param or something else
>
Ah, OK; In that case - you cannot set a rowcount, so you have three options:
1. Set the return type of your SQLCLR based proc to integer and return
the collected rowcount as the return value.
2. Have an out param in your SQLCLR based proc and assign the rowcount
to that param.
3. Execute as last statement in your SQLCLR based proc a select against
the collected rowcount and let the users of the SQLCLR based proc
execute it with ExecuteScalar

Personally, I'd choose between 1 and 2, where - unless you use return
values for something else in your code - I'd prefer number 1.

Niels