How can I reuse a Common Table Expression
If you do not require them in 2 different queries, you can try
;with query as (
Select Row_Number() over (Order By UserID ASC) as TableRowNum,
FirstName,
LastName
From Users
),
totalCount AS (
SELECT COUNT(1) Total FROM query
)
Select query.*,
Total
from query, totalCount
where TableRowNum
between 1 and 25
Order By TableRowNum ASC
If you do require 2 different queries, rather use a table var
DECLARE @User TABLE(
TableRowNum INT,
FirstName VARCHAR(50),
LastName VARCHAR(50)
)
;with query as (
Select Row_Number() over (Order By UserID ASC) as TableRowNum,
FirstName,
LastName
From Users
)
INSERT INTO @User
SELECT TableRowNum,
FirstName,
LastName
FROM query
SELECT *
FROM @User
where TableRowNum
between 1 and 25
Order By TableRowNum ASC
SELECT COUNT(1) FROM @User
Reuse results from SQL Server common table expression
From the WITH common_table_expression
manual:
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
So, no, you can't extend the scope of the CTE beyond the SELECT statement it was defined in. You will have to store the result in a temporary table or table valued variable if you want to use the result more than once.
Use one CTE many times
A CTE
is basically a disposable view. It only persists for a single statement, and then automatically disappears.
Your options include:
Redefine the
CTE
a second time. This is as simple as copy-paste fromWITH...
through the end of the definition to before yourSET
.Put your results into a
#temp
table or a@table
variableMaterialize the results into a real table and reference that
Alter slightly to just
SELECT COUNT
from your CTE:
.
SELECT @total = COUNT(*)
FROM Players p
INNER JOIN Teams t
ON p.IdTeam=t.Id
INNER JOIN Leagues l
ON l.Id=t.IdLeague
WHERE l.Id=@idleague
How to reference one CTE twice?
You can use commas to create multiple CTEs that references the CTEs Above.
Just to illustrate what I mean:
with recs as (
select
*,
row_number() over (order by id) as rownum from ......
),
counts as (
select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts
where rownum between @a and @b ....
This is not the a good solution.
The best solution I found to have the total count in a CTE without counting the records is described in this article.
DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq,
ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq
Related Topics
Is There a Tool for Refactoring SQL, a Bit Like a Resharper for SQL
Should a Composite Primary Key Be Clustered in SQL Server
"Similar Posts" Like Functionality Using Ms SQL Server
How to Add New Column in Existing View in SQL-Server 2014 Using Alter
Table Valued Function Where Did My Query Plan Go
Do I Need to Call Rollback If I Never Commit
Does Except Execute Faster Than a Join When the Table Columns Are the Same
Many-To-Many Relations in Rdbms Databases
Distance Between Two Coordinates, How to Simplify This And/Or Use a Different Technique
Ssrs Report Builder - Only Show Header on First Page (With Page Numbers)
Bigquery: How to Group and Count Rows Within Rolling Timestamp Window
SQL Do Inner Join If Condition Met
How to Compare Two Columns in the Same Table
Conversion Failed When Converting from a Character String to Uniqueidentifier