Link to home
Start Free TrialLog in
Avatar of KentDBerry
KentDBerry

asked on

Dynamic Query Error: Is Not a Valid Identifier

I need help creating a dynamic query that references table valued functions that have parameters passed.  The below dynamic query generates the error below the query.  Please advise how to resolve:

Declare @SQL nVarChar(max)

Set @SQL =
'SELECT V.YR AS [Budget Year], V.Project_Number, V.Task
FROM  dbo.vEFF_NAFTA_WORKSLATE_REPORT AS V       
        LEFT OUTER JOIN dbo.tvfLatestStatus('+ cast(@HeaderID as nvarchar) + ') AS LS ON V.Trial_NO = LS.TRIAL_NUMBER_NAME
      LEFT OUTER JOIN dbo.tvfGDBLoad(' + cast(@HeaderID as nvarchar) + ') AS GL ON V.Trial_NO = GL.TRIAL_NUMBER
        LEFT OUTER JOIN  tvfCountPercentages(#' + Convert(nvarchar(max), @YearEntered, 101) + '#) AS P ON V.Trial_NO = P.TRIAL_NUMBER_NAME
      LEFT JOIN tvfCopStat(' + cast(@HeaderID as nvarchar) + ', ' + cast(@Year as nvarchar) +  ') AS C ON V.Trial_NO = C.[TRIAL NUMBER]
WHERE       V.YR=' + cast(@Year as nvarchar)

********************************************************************
Msg 203, Level 16, State 2, Procedure spIH_COI_CRO_Workslate, Line 115
The name 'SELECT V.YR AS [Budget Year],       V.Project_Number, V.Task
FROM  dbo.vEFF_NAFTA_WORKSLATE_REPORT AS V       
        LEFT OUTER JOIN dbo.tvfLatestStatus(1) AS LS ON V.Trial_NO = LS.TRIAL_NUMBER_NAME
      LEFT OUTER JOIN dbo.tvfGDBLoad(1) AS GL ON V.Trial_NO = GL.TRIAL_NUMBER
        LEFT OUTER JOIN  tvfCountPercentages(#12/31/2006#) AS P ON V.Trial_NO = P.TRIAL_NUMBER_NAME
      LEFT JOIN tvfCopStat(1, 2007) AS C ON V.Trial_NO = C.[TRIAL NUMBER]
WHERE       V.YR=2007' is not a valid identifier.

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KentDBerry
KentDBerry

ASKER

Since you are assuming that I run the query like that, I am going to assume that I am not the first person to have done this.  That was indeed the problem

Thanks.
>Since you are assuming that I run the query like that, I am going to assume that I am not the first person to have done this. That was indeed the problem
:) yes, indeed.
glad I could help