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.
Due to the front-end architecture I need to be able to return the number of results that would come back without actually returning the results. The front end would then call the stored procedure a second time to get the actual results.
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.
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.
What are people's thoughts on these approaches? Has anyone solved this problem before in a way that I haven't thought of?
They CANNOT take the results and count at the same time from a single call.
Thanks!
I personally go with the two query approach, yes, you have to maintain the search logic in two places, but I've found that the performance optimization benefit, and overall cleanness of the code pays off in the end.
Using a flag passed to a single procedure, is a potential solution, but I just find that very hard to maintain, especially for complex search logic.
The route of using temporary tables etc, that just adds WAY more overhead than what is needed.
Thus, why I have landed with the two query method. Everything that I find online recommends this approach as well.