how to EXEC a multiline SELECT?

The following exec is generating this exception:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '
with
cte_rag as
(select
 rc.RagColumnId
,rc.RagId
,rc.Header
,rc.FormElementId
,rc.BaseObjectColumnName
,b.BaseObjectTy'.

Completion time: 2021-11-29T12:58:41.5681063-06:00

Here’s the full 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
    '''

    exec(@sql)

What am I doing wrong? How do we run this exec command?

>Solution :

Your SQL has too many quotes added at the start and end, you don’t need to encase the dynamic SQL in its own quotes, remove the ''' at the start and end, just simply do

set @sql = '
with
cte_rag as...
...
and cs.projectid = p.ProjectId
'