Common Table Expression and Execute

I have developed below

DECLARE @dbname VARCHAR(40)
SET @dbname = ( SELECT TOP 1
                        m.archetypecode + CONVERT(NVARCHAR, runindex)
                FROM   dbo.nxMetricRun mr
                        INNER JOINdbo.nxMetric m ON mr.MetricID = m.nxMetricID
                                                 AND mr.MetricTableName IS NULL
                WHERE   mr.Description = 'Matter Summary - Scheduled'
                ORDER BY mr.timestamp DESC
              )
   

EXECUTE ('select  mt.mattindex, sum(billfees) BillFees, m.wipbal,c.clibal, mt.number,mt.Displayname FeeEarner,MattStatus,mt.OpenDate,mt.CloseDate
from ' + @dbname + ' m
inner join  dbo.MattDate md on m.MattDate = md.MattDateID ')

I would now like to put it in a CTE to use it with other database tables

; with cte as (EXECUTE ('select  mt.mattindex, sum(billfees) BillFees, m.wipbal,c.clibal, mt.number,mt.Displayname FeeEarner,MattStatus,mt.OpenDate,mt.CloseDate
from ' + @dbname + ' m
inner join  dbo.MattDate md on m.MattDate = md.MattDateID '))


Is this possible before I proceed any further and if so how?

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can not use the EXECUTE withing CTE since in CTE you can only use precomputed result so what you can do is you can have table variable which will store the result of your dynamic execute query which you can use in future as CTE

DECLARE @TBL TABLE (TYOSUHDE_ID FLOAT,MAKSAVA_YKSIKKO_ID FLOAT)


INSERT INTO @TBL
EXECUTE ('SELECT TYOSUHDE_ID,MAKSAVA_YKSIKKO_ID FROM [StagingAreaKPIW].[TYOSUHDE]')

;WITH CTE AS
(
SELECT * FROM @TBL
--EXECUTE ('SELECT TYOSUHDE_ID,MAKSAVA_YKSIKKO_ID FROM [StagingAreaKPIW].[TYOSUHDE]')
)
, CTE2 AS
(
SELECT * FROM CTE WHERE MAKSAVA_YKSIKKO_ID = 5
)

SELECT * FROM CTE2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CTE cannot be used with EXECUTE ...

what you could do is to use a staging/temp table:

create table #t1 (name sysname)
insert into #t1
execute('select name from sysobjects')
drop table #t1
0
ste5anSenior DeveloperCommented:
Yup, use a temporary table. E.g.
DECLARE @dbname SYSNAME = ( SELECT TOP 1
                                    D.name
                            FROM    sys.databases D
                            WHERE   D.name != 'tempdb'
                            ORDER BY D.create_date DESC
                          );

DECLARE @SQL VARCHAR(MAX) = '
INSERT INTO #Results
	SELECT	''?'' AS db_name,
		T.name ,
		T.object_id ,
		T.schema_id           
	FROM ?.sys.tables T;
';

CREATE TABLE #Results
    (
      db_name SYSNAME ,
      table_name SYSNAME ,
      object_id INT ,
      schema_id INT
    );

SET @SQL = REPLACE(@SQL, '?', @dbname);
EXECUTE (@SQL); 

SELECT  *
FROM    #Results R;

DROP TABLE #Results;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.