How to count no of record ?

[Agnes]

In my store procedure , i will select * from myTable, and I will while-loop
the result by using datareader in vb.net .
Now, Can I know the number of records before I while-loop the resut ??

 

[Imran Koradia]

AFAIK, you won't know the number of records. You can change the query to
say:

Select *, Count(*) from myTable which will also give you the count.

hope that helps..
Imran.

 

[CT]

Not unless you have the row count returned as a different result set, making
the stored procedure return two results; the row count and the actual rows.

 

[Agnes]

Could you give me a little sample , how to return two result ???
Thanks a lot

 

[CT]

Sure, do something like this:

SELECT COUNT(*) FROM TableName;
SELECT * FROM TableName

Once results have been returned to the client, read the scalar value in the
first resultset, and move the "real" data in the second resultset using the
NextResult method of the datareader.

 

[Imran Koradia]

why not put the * and count(*) in the same sql so you don't hit the server
twice?

select *, Count(*) from myTable

 

[Imran Koradia]

disregard my previous msg - I wasn't thinking

Sure, do something like this:

SELECT COUNT(*) FROM TableName;
SELECT * FROM TableName

Once results have been returned to the client, read the scalar value in the
first resultset, and move the "real" data in the second resultset using the
NextResult method of the datareader.

 

[CT]

Heck, it's Friday, right? ;-)