CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Grenville
Grenville

Reputation: 245

SQL Server CTE to select and also return value

With SQL Server 2008, I am using a common table expression to select a list of properties (villas) matching given criteria, then giving row numbers and selecting a specific 'page' based on the row numbers. It is working well, but I'd also like to return the total number of rows (i.e. ignoring the subsequent paging). The CTE seems to only have a single use following its declaration. I have tried doing the following (super simplified version);

WITH results AS (
    SELECT property.* FROM property 
)

SELECT * FROM results

RETURN SELECT COUNT(results.id)

But it loses reference to 'results' and errors;

The multi-part identifier "results.id" could not be bound.

Is there an efficient way to do this, that does not involve double querying?

To clarify, I want to both select the rows, and ALSO return the total number of outer rows. But primarily I have hit a roadblock with the CTE seemingly not existing after the first use.

To help show M.Ali what I'm aiming for in the full solution;

DECLARE @pageSize int;
DECLARE @pageNumber int;
DECLARE @totalResults int;

SET @pageSize = 30;
SET @pageNumber = 1;

;WITH results AS (
    SELECT property.*   
    FROM property 
),

/* add the row number based on sorting */
/* this is where I need to store into @totalResults, but I can't break the CTE chain */

weightedResults AS (
    SELECT 
        *, 
        ROW_NUMBER() OVER(ORDER BY results.id) AS rowNum

    FROM 
        results
)

/* do the paged select */
SELECT * FROM weightedResults
WHERE (rowNum > @pageSize * (@pageNumber-1)) AND (rowNum <= (@pageSize * (@pageNumber-1)) + @pageSize)
ORDER BY rowNum

See the comment where I state that I need to collect the total rows - that is the point I need to store the total rows from 'results', but if I select at this point then I consume the CTE. Can I make the CTE more permanent?

Upvotes: 2

Views: 2733

Answers (1)

M.Ali
M.Ali

Reputation: 69574

;WITH results 
AS (

    SELECT property.* FROM property 
  )
  SELECT COUNT(*) FROM results

OR

Simply use @@ROWCOUNT function to get the total number of rows returned after you have executed you select statement.

/* Your Select Statement here */

SELECT @@ROWCOUNT;

Upvotes: 2

Related Questions