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?

  • 1
    I'd appreciate if the downvoters shared the reason for downvoting the question. – julealgon Oct 19 at 21:12
  • What's your version of SQL Server? Do you want to avoid duplicating the code for performance or style reasons? – Joe Obbish Oct 20 at 1:07
  • @joeobbish SqlServer 2014. I want to avoid duplication for maintenance reasons. – julealgon Oct 21 at 2:10
  • Thanks for adding the version-specific tag @JoeObbish, that makes a lot of sense and I completely forgot about it. – julealgon Oct 22 at 12:52
up vote 0 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.