How can we use Multiple Queries using 'WITH' Clause

Question

as sample code below....

 WITH sampleA as (SELECT * FROM emp)

 SELECT * FROM sampleA

 SELECT * FROM sampleA

this alias 'sampleA' will work for only first query not for second or later..

But I want to query more with this alias only.

Can you please help me, how can I do that?

Answer 1

Common table Expression scope is limited to first SELECT statement. For multiple usage , use instead temporay table or table variable.