T-SQL Return row affected count in CLR proc?

  • Thread starter Thread starter Smokey Grindel
  • Start date Start date
S

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!
 
Smokey said:
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!
Click to expand...
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
 
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
 
Smokey said:
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
Click to expand...

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
 
Yes its executed using the executenonquery but it always returns zero... and
SET NOCOUNT is set to off
 
Smokey said:
Yes its executed using the executenonquery but it always returns zero... and
SET NOCOUNT is set to off
Click to expand...

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
 
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
 
Smokey said:
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
Click to expand...

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
 
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
 
Smokey said:
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
Click to expand...
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
 
Back
Top