CODEWITHSUNDEEP
sqlsql-servert-sql
Timofei Davydik
Timofei Davydik

Reputation: 7294

How to use WITH result table multiple times?

DECLARE @count INT;
WITH CTE AS
(SELECT...)
SELECT @count = COUNT(ID) FROM CTE;
SELECT * FROM CTE

Can I use CTE table after first expression SELECT @count = COUNT(ID) FROM CTE? I get error Invalid object name 'CTE'. I'm using SQL Server 2008.

Thanks

Upvotes: 0

Views: 158

Answers (2)

alvarorahul
alvarorahul

Reputation: 89

You could use a table variable and use it everywhere.

DECLARE @count INT;
DECLARE @CTE AS TABLE (<<columns>>)

INSERT INTO @CTE (<<columns>>)
SELECT ....

WITH CTE AS
(SELECT * FROM @CTE)
SELECT @count = COUNT(ID) FROM CTE;

SELECT * FROM @CTE

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

You can't, really; the CTE only exists for the scope of a single statement. However in this case you could do this:

DECLARE @count INT;

WITH CTE AS (SELECT...)
SELECT * FROM CTE;

SELECT @count = @@ROWCOUNT;

If you need the count as part of the resultset, then you can just say:

WITH CTE AS (SELECT...)
SELECT *, COUNT(*) OVER() FROM CTE;

If you need the count for other reasons, please describe them.

Upvotes: 4

Related Questions