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_R EPORT 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_R EPORT 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.
Declare @SQL nVarChar(max)
Set @SQL =
'SELECT V.YR AS [Budget Year], V.Project_Number, V.Task
FROM dbo.vEFF_NAFTA_WORKSLATE_R
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_R
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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
:) yes, indeed.
glad I could help
ASKER
Thanks.