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 FROM
+ WHERE
sections?
My FROM
and 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?