Sql-server – Avoiding code duplication for counting and selecting the same resultset

pagingsql serversql server 2014stored-procedures

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?

Best Answer

I was checking ways to make this work and it appears that an inline table valued function is a decent option (even though I'd prefer something that didn't add extra stuff to the database).

CREATE FUNCTION [dbo].[myFunction] 
(   
    @filter1 int,
    @filter2 int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT (alias1.field1 as Key, alias2.field2, alias3.field3)
    FROM <Complex joins + where @filter1/@filter2>
)

By extracting the main complexity into the function, I can now reuse it for both queries easily enough:

CREATE PROCEDURE [dbo].[myProcedure] 
    @includeCount bit,
    --... where parameters
    @skip int,
    @take int,
    @totalCount int OUTPUT
AS
BEGIN  
    IF (@includeCount = 1)
        SELECT @totalCount = COUNT(*) FROM [dbo].[myFunction](where parameters)

    SELECT *
    FROM [dbo].[myFunction](where parameters)
    ORDER BY [Key]
    OFFSET @skip ROWS
    FETCH NEXT @take ROWS ONLY
END
GO

There is still some form of duplication due to the parameter passing, but I guess that's unavoidable as there is no such thing as a closure in SQL.

The ORDER BY needing to be outside the function is also not very intuitive, but SQL doesn't allow me to specify it by itself in the procedure without carrying over the paging statements.

This works for me so I'm marking my own answer as the answer for now. Obviously open to better suggestions if there are any.

Related Question