CODEWITHSUNDEEP
sql-server-2008t-sql
codingManiac
codingManiac

Reputation: 1700

Invalid Object Name -- SQL Server 2008

Right now I am getting an error of 'Invalid object name qryTI_3'. I only get this error when I try to execute the entire procedure, but I can still save the procedure. I have verified that all the fields do exist in the table referenced. What is wrong with this query?

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);


WITH qryTI_1 AS 
(
SELECT tblTSF.fldFPK, tblTSF.fldPK, tblTSF.fldCI, tblTSF.fldNI
FROM tblTSF
WHERE (((tblTSF.fldMN)<>-254))
),

qryTI_2 AS 
(
SELECT tblTSF.fldFPK, tblTSF.fldPK, tblAL.fldLN AS fldCI, 0 AS fldNI
FROM tblAL, tblTSF
GROUP BY tblTSF.fldFPK, tblTSF.fldPK, tblAllLevels.fldLN
HAVING (((tblAL.fldLN)<>0 And (tblAL.fldLN)<>6))
),

qryTI_3 AS 
(
SELECT * FROM qryTI_1 UNION SELECT * FROM qryTI_2
)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(fldCI) 
                    from qryTI_3
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT fldPK, fldFPK, ' + @cols + '
                FROM 

                (
                SELECT fldPK
                        ,fldFPK
                        ,fldNI
                        ,fldCI
                FROM qryTI_3    
                ) as x
                PIVOT
                (
                    Sum(fldNI) FOR fldCI IN (' + @cols + ')
                ) as p '

        execute sp_executesql @query

Upvotes: 2

Views: 684

Answers (1)

MatBailie
MatBailie

Reputation: 86706

A CTE only exists for the duration of that statement.

   WITH
     cte1 AS
   (blah)
   ,
     cte2 AS
   (blah)
   ,
     cte3 AS
   (blah)

   statement1;  -- The CTE definitions are part of this statement 

   statement2;  -- They are not part of this statement, they're gone by now

So, once your SET statement completes, the CTE is gone too, before you ever reach the execute call.

Not to mention the fact that your call to execute has also created a new scope, within which the CTE and variables don't exist either.

Try making these views instead of CTEs - So that they exist for more than one statement, and are visible within the scope created by your call to execute.

Upvotes: 4

Related Questions