T-SQL Return row affected count in CLR proc?

[Smokey Grindel]

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 Berglund]

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 Grindel]

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]

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 Grindel]

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

 

[Niels Berglund]

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 Grindel]

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]

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 Grindel]

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]

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