title: Getting counts for a paged SQL search stored procedure
body: <p>I've written a paged search stored procedure using SQL Server 2005. It takes a number of parameters and the search criteria is moderately complex.</p>
  
  <p>Due to the front-end architecture I need to be able to return the number of results that would come back <strong>without</strong> actually returning the results. The front end would then call the stored procedure a second time to get the actual results.</p>
  
  <p>On the one hand I can write two stored procedures - one to handle the count and one to handle the actual data, but then I need to maintain the search logic in at least two different places. Alternatively, I can write the stored procedure so that it takes a bit parameter and based on that I either return data or just a count. Maybe fill a temporary table with the data and if it's count only just do a count from that, otherwise do a select from it. The problem here is that the count process could be optimized so that's a lot of extra overhead it seems (have to get unneeded columns, etc.). Also, using this kind of logic in a stored procedure could result in bad query plans as it goes back and forth between the two uses.</p>
  
  <p>The amount of data in the system isn't too high (only a couple million rows for even the larger tables). There may be many concurrent users though.</p>
  
  <p>What are people's thoughts on these approaches? Has anyone solved this problem before in a way that I haven't thought of?</p>
  
  <p>They <strong>CANNOT</strong> take the results and count at the same time from a single call.</p>
  
  <p>Thanks!</p>