如何使用EXEC执行多行SELECT，解决动态SQL语法报错问题

• 动态SQL字符串首尾存在多余的单引号：你对@sql变量赋值时，开头使用了三个单引号、结尾使用三个单引号闭合，最终存入@sql的SQL语句会被额外的一对单引号包裹，执行时SQL Server会将这对多余的单引号识别为语法的一部分，直接触发语法错误，和你收到的报错信息完全吻合。
• 补充注意点：如果后续SQL语句继续扩展长度，要始终保持@sql为nvarchar(max)类型，避免字符串被隐式截断导致语法错误。

1. 将SET @sql = 后面开头的三个单引号改为1个，将SQL语句末尾的三个单引号改为1个，保留语句内部用于转义的双单引号（比如''yyyy-MM-dd''无需修改）
2. 调试时可以在exec(@sql)前加一行PRINT @sql，打印出生成的SQL语句直接检查是否符合预期。

declare @sql nvarchar(max) 
set @sql = '
with
cte_rag as
(select
 rc.RagColumnId
,rc.RagId
,rc.Header
,rc.FormElementId
,rc.BaseObjectColumnName
,b.BaseObjectTypeId
,b.BaseObjectTypeName
,b.TableName
from rag.rags r
join rag.RagColumns rc
on r.RagId = rc.ragid
join rag.BaseObjectTypes b
on r.BaseObjectTypeId = b.BaseObjectTypeId
),
cte_scalar_data (projectid,formdataid,formlementid,displayvalue) as (
SELECT
    p.ProjectId,
    fd.formdataid,
    t.FormElementId,
    (CASE
            WHEN t.FormDataAdapterId IS NOT NULL
            THEN dbo.GetFormDataAdapterValue(t.FormDataAdapterId, se.Value)
            ELSE
                ISNULL(
                CASE WHEN t.FormElementTypeId IN (4,5)
                THEN
                    dbo.GetOptionCaption(t.FormElementId, se.Value)
                ELSE
                    CASE WHEN t.FormElementTypeId = 3
                    THEN
                        FORMAT(CONVERT([datetime], se.Value), ''yyyy-MM-dd'')
                    ELSE
                        CASE WHEN t.FormElementTypeId = 7
                        THEN
                            se.Value2
                        ELSE
                            se.Value
                        END
                    END
                END, t.OptionCaption)
        END) AS DisplayValue
FROM
    Projects p
    INNER JOIN FormData fd ON fd.ObjectId = p.ProjectId
    INNER JOIN
    (SELECT
        e.FormId,
        e.FormElementId,
        e.FormElementTypeId,
        e.Name,
        e.Title,
        da.FormDataAdapterId,
        e.Title AS ValueName,
        o.Caption AS OptionCaption,
        e.PermanentName
    FROM
        FormElements e
        LEFT JOIN FormElementDataAdapters da ON e.FormElementId = da.FormElementId
        LEFT JOIN FormElementOptions o ON o.FormElementId = e.FormElementId AND o.IsDefault = 1
    WHERE e.FormElementId IS NOT NULL AND e.FormElementTypeId IN (1,3,4,5,7) ) t
    ON t.FormId = fd.FormId
    LEFT JOIN FormScalarElementsData se ON fd.FormDataId = se.FormDataId AND t.FormElementId = se.FormElementId
)

select r.header
      ,r.FormElementId
      ,r.BaseObjectColumnName
      ,r.BaseObjectTypeName
      ,fe.FormId
      ,fd.ObjectId
      ,p.projectid
      ,c.cipid
      ,fd.FormDataId
      ,cs.displayvalue
from cte_rag r
left join formelements fe
on fe.FormElementId = r.FormElementId
left join formdata fd
on fd.FormId = fe.FormId
left join dbo.projects p
on fd.objectid = p.projectid
left join dbo.cips c
on fd.objectid = c.CipId
left join cte_scalar_data cs
on      cs.formdataid = fd.FormDataId
    and cs.formlementid = r.FormElementId
    and cs.projectid = p.ProjectId
'
PRINT @sql -- 调试用，确认生成的SQL正确后可以删除
exec(@sql)

内容的提问来源于stack exchange，提问作者Alex Gordon