problem in executing dynamic query using pivot with syntax error Incorrect syntax near ' + '

Question

I want to Create a query with dynamic query and in this query i use concat some cells , when use concat like 'N'subject:' + ' ' + ContentProductionTitle' i get syntax error

error : Incorrect syntax near ' + '.

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.KeyWordTitle) 
           FROM TBL_CU_ContentProduction_KeyWord c
           FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
          (
             select  
  isnull((select   N'subject:' + '  ' +  ContentProductionTitle  
    from Tbl_Cu_ContentProductionCalendar 
    where ContentProductionCalendarID = a.ActionSubjectId   ),'')
  +' ' + 
   isnull((select top 1   N'author:'+  p.FullName + '  '    from users.TblProfiles  as p
   where p.UserId= a.ActionUserID ),'') +' ' + 
    
    isnull((select top 1  N'edit bye:' + '  ' + p.FullName  from users.TblProfiles as p 
      where p.UserId= a.CheckAgain ) ,'')  as title 
      , a.ActionDateTo
      , k.KeyWordTitle 

   from TBL_CU_ContentProduction_KeyWord  as k 
    inner join Tbl_CU_ContentProduction as cp
    on k.ContentGUID = cp.ContentGUID 
    inner join Tbl_Cu_ActionContentProduction as a 
    on a.ContentGUID = cp.ContentGUID
          ) x
           pivot 
           (
                max(ActionDateTo)
                
               for KeyWordTitle in (' + @cols + ')
           ) p '


execute(@query)

Please actually ask a question within the question. And the way you debug dynamic SQL is you `PRINT(@query)` instead of executing it, then you have static SQL to debug. — Dale K, Jun 08 '21 at 04:21
And if you still have issues post the output from your print statement... — Dale K, Jun 08 '21 at 04:38
If you're using SQL Server 2017 or later then you should use `STRING_AGG` instead of `FOR XML`. — Dai, Jun 08 '21 at 04:40
Also, you should use `sp_executesql` instead of `EXECUTE` for Dynamic SQL: https://dba.stackexchange.com/questions/165149/exec-vs-sp-executesql-performance — Dai, Jun 08 '21 at 04:41

score 1 · Answer 1 · answered Jun 08 '21 at 04:41

you need to escape single quote inside your query string with '':

set @query = 'SELECT ' + @cols + ' from 
          (
             select  
  isnull((select   N''subject:'' + ''  '' +  ContentProductionTitle  
    from Tbl_Cu_ContentProductionCalendar 
    where ContentProductionCalendarID = a.ActionSubjectId   ),'''')
  +'' '' + 
   isnull((select top 1   N''author:''+  p.FullName + ''  ''    from users.TblProfiles  as p
   where p.UserId= a.ActionUserID ),'''') +'' '' + 
    
    isnull((select top 1  N''edit bye:'' + ''  '' + p.FullName  from users.TblProfiles as p 
      where p.UserId= a.CheckAgain ) ,'''')  as title 
      , a.ActionDateTo
      , k.KeyWordTitle 

   from TBL_CU_ContentProduction_KeyWord  as k 
    inner join Tbl_CU_ContentProduction as cp
    on k.ContentGUID = cp.ContentGUID 
    inner join Tbl_Cu_ActionContentProduction as a 
    on a.ContentGUID = cp.ContentGUID
          ) x
           pivot 
           (
                max(ActionDateTo)
                
               for KeyWordTitle in (' + @cols + ')
           ) p '

problem in executing dynamic query using pivot with syntax error Incorrect syntax near ' + '

1 Answers1