SELECT *,(SELECT COUNT(*) FROM Customer WHERE ID=7)
FROM Customer WHERE ID=7
If using stored procedures, perhaps you can do this more elegantly.
Why do you need the count?|||You can also check the @.@.rowcount system variable after the select
select ...
set @.Count = @.@.rowcount|||I need the count because the project I am working on needs to do specific tasks dependent on the no of records returned.
How can I access the @.@.rowcount from inside my code?|||Well, then I think the reality is you are going to have to (either in a seperate query or in a subquery) run the COUNT(*) query and send that as a column in the select statement. You could pass an OUTPUT parameter, but I do not believe the OUTPUT parameter will be readable until the DataReader has been read through.
Perhaps you should just use a DataSet, which has the count available.|||Perhaps a dataset is the better tool for the job then. Will it have any significant overhead compared to a datareader?|||Yes. Rather than reading records one at a time in firehose mode, it will read all rows into memory, and store them in memory (the dataSet is an in-memory representation). If you are processing a huge resultset, it could be a problem vs. the DataReader, but generally it is OK as well.|||You can also try using a stored procedure and you can return the count as either a seperate resultset or using an output paramater.
