CODEWITHSUNDEEP
c#t-sqlvisual-studio-2013
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Set rowcount for specific count?

I have a stored procedure that looks like this:

create procedure usp_FilTable
( 
    @Today datetime
)
as 

truncate table MyTable;

insert into MyTable (col1, col2)
select col1, col2 from MyTableStaging

delete from MyTable where ReportDate <> @Today

Update MyTable set Col1 = 'Sale' where Col2 = 'Salesman'


/* set ROWCOUNT = count(*) from MyTable */

And I call it like so:

int rows = SqlHelper.ExecuteNonQuery(connString, CommandType.Text, query);

rowcount counts all rows affected by the SP, so variable rows will always be incorrect.

How can I set rowcount to count(*) from MyTable?

Upvotes: 0

Views: 259

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Instead of inserting, then deleting and then updating some of the rows, I would like to suggest a different SQL approach:

create procedure usp_FilTable
( 
    @Today datetime -- Are you sure this should be a dateTime value, not just a Date?
)
as 

truncate table MyTable;

insert into MyTable (col1, col2)
select iif(Col2 = 'Salesman', 'Sale', col1), col2 
from MyTableStaging
where ReportDate <> @Today

This will have the same effect with just a single query - making it both faster and much more readable.

As to your actual question: The return value of ExecuteNonQuery should return the number of rows effected, but if I remember correctly, in the case of a multi-statement procedure it returns the number of rows effected by the last statement.
The SET ROWCOUNT statement have a different effect that what you think - it limits the number of rows returned from a select statement (in the current version, it also effect insert, update and delete statements, but that's going to change).

You can always add an output parameter to your stored procedure, or simply end it with select count(*) from MyTable and run it from c# using ExecuteScalar.

Upvotes: 2

Related Questions