I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is
true. How do I perform the 2 queries (counting + paged result) without having to replicate the entire
WHERE are non-trivial, so I'd like to avoid having to duplicate them across the 2 queries. My primary goal is avoiding duplicate code as opposed to improving performance.
This is what I have currently:
CREATE PROCEDURE [dbo].[myProcedure] @includeCount bit, --... where parameters @skip int, @take int, @totalCount int OUTPUT AS BEGIN IF (@includeCount = 1) SELECT @totalCount = COUNT(*) FROM <Complex joins + where> SELECT (alias1.field1, alias2.field2, alias3.field3) FROM <Complex joins + where> ORDER BY [alias1].[Key] OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY END GO
Is there a way to "extract" the common parts (the FROM+SELECT statements) in a way as to avoid the massive duplication while retaining the same performance characteristics of the original queries? Would it pose a problem to my
ORDER BY and
SELECT sections which depend on aliased tables inside
FROM? How would I access those?