Using result of a SQL WITH statement more than once in a stored procedure

Question

I have the SQL stored procedure below:

ALTER PROCEDURE [dbo].[sp_getpaginatedusersbyglobalfilter] 
    @globalFilter varchar(max)
    , @pageSize int
    , @page int
    , @totalRecords int output
    , @totalFilteredRecords int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    ;WITH PaginatedUsers AS(
        SELECT     ROW_NUMBER() OVER(ORDER BY u.LastName) ID
                   , u.Username
                   , u.FirstName
                   , u.OtherNames
                   , u.LastName
                   , e.Nombre
                   , r.RoleName
                   , u.PhoneNumber
        FROM       webpages_Users u
        INNER JOIN Establecimiento e
        ON         u.FacilityId = e.IDReporte
        AND        e.EstadoRegistro = 1
        INNER JOIN webpages_UsersInRoles ur
        ON         u.UserId = ur.UserId
        INNER JOIN webpages_Roles r
        ON         ur.RoleId = r.RoleId
        WHERE      u.Username LIKE '%' + @globalFilter + '%'
        OR         u.FirstName LIKE '%' + @globalFilter + '%' 
        OR         u.OtherNames LIKE '%' + @globalFilter + '%'
        OR         u.LastName LIKE '%' + @globalFilter + '%'
        OR         e.Nombre LIKE '%' + @globalFilter + '%'
        OR         r.RoleName LIKE '%' + @globalFilter + '%'
        OR         u.PhoneNumber LIKE '%' + @globalFilter + '%'
    )

    SELECT  pu.ID, pu.Username
            , pu.FirstName
            , pu.OtherNames
            , pu.LastName
            , pu.Nombre AS FacilityName
            , pu.RoleName
            , pu.PhoneNumber
    FROM    PaginatedUsers pu
    WHERE   pu.ID BETWEEN ((@page - 1) * @pageSize + 1)
    AND     @page * @pageSize

    SELECT @totalFilteredRecords = COUNT(pu.ID)
    FROM    PaginatedUsers pu

    SELECT @totalRecords = COUNT(u.UserId)
    FROM   webpages_Users u
END

The problem I have is with the section:

    SELECT @totalFilteredRecords = COUNT(pu.ID)
    FROM    PaginatedUsers pu

The error I get is "Invalid object name 'PaginatedUsers'". As per my research, I cannot use the PaginatedUsers (resulting from the WITH) object more than once in the query. Then, How can I do to return the total number of records in the output variable @totalFilteredRecords? I've been thinking about inserting the result into a temporal table and then get the count from it as well as the final resultset, but I don't like that idea. What can I do?

Thanks.

Answer 1

You can only use the output of a With clause in the same statement as the With clause. The first Select clause is part of the first SQL Statement that starts with the With clause. By using a second Select, it is in a separate SQL statement. If you connect the two Selects into a single statement, then it will work.

Answer 2

Your research is correct, you cannot use CTEs more than once.

--EDIT

AS OP points out, A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

So I don't have any other suggestions rather than to use a temp table -- here is an untested example:

SELECT     ROW_NUMBER() OVER(ORDER BY u.LastName) ID
               , u.Username
               , u.FirstName
               , u.OtherNames
               , u.LastName
               , e.Nombre
               , r.RoleName
               , u.PhoneNumber
       INTO #PaginatedUsers
FROM       webpages_Users u
    INNER JOIN Establecimiento e
    ON         u.FacilityId = e.IDReporte
    AND        e.EstadoRegistro = 1
    INNER JOIN webpages_UsersInRoles ur
    ON         u.UserId = ur.UserId
    INNER JOIN webpages_Roles r
    ON         ur.RoleId = r.RoleId
WHERE      u.Username LIKE '%' + @globalFilter + '%'
    OR         u.FirstName LIKE '%' + @globalFilter + '%' 
    OR         u.OtherNames LIKE '%' + @globalFilter + '%'
    OR         u.LastName LIKE '%' + @globalFilter + '%'
    OR         e.Nombre LIKE '%' + @globalFilter + '%'
    OR         r.RoleName LIKE '%' + @globalFilter + '%'
    OR         u.PhoneNumber LIKE '%' + @globalFilter + '%'


SELECT  pu.ID, pu.Username
        , pu.FirstName
        , pu.OtherNames
        , pu.LastName
        , pu.Nombre AS FacilityName
        , pu.RoleName
        , pu.PhoneNumber
FROM    #PaginatedUsers pu
WHERE   pu.ID BETWEEN ((@page - 1) * @pageSize + 1)
AND     @page * @pageSize

SELECT @totalFilteredRecords = COUNT(pu.ID)
FROM    #PaginatedUsers pu

SELECT @totalRecords = COUNT(u.UserId)
FROM   webpages_Users u

DROP TABLE #PaginatedUsers

Here is a good answer I read about CTE vs Temp Tables.