动态T-SQL WITH语句错误

I am building a dynamic T-SQL script and running into some errors.

我正在构建一个动态的T-SQL脚本并遇到一些错误。

    PRINT 'Convert References into XML fields'
    SET @query = N';WITH DCODES AS (' +
                'SELECT [id],[Codes],' +
                'CAST(''<M>'' + REPLACE([Codes],'','' , ''</M><M>'') + ''</M>'' AS XML) AS [XML_Codes],' +
                'CAST(''<M>'' + REPLACE(REPLACE([Descriptions],''&'',''AND''),'','',''</M><M>'') + ''</M>'' AS XML) AS [XML_Desc] ' +
                'FROM  ##' + @system_name + '_Temp)'

 EXEC sp_executesql @query   

The code works if I write the statement as a static statement. When I run this in my dynamic script this error.

如果我将语句编写为静态语句,则代码有效。当我在动态脚本中运行此错误时。

--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near ')'.

I tried to rewrite it, but I still get this issue.

我试图重写它,但我仍然遇到这个问题。

1 个解决方案

#1

missing line below

下面缺少一行

SET @query = N';WITH DCODES AS (' +
                'SELECT [id],[Codes],' +
                'CAST(''<M>'' + REPLACE([Codes],'','' , ''</M><M>'') + ''</M>'' AS XML) AS [XML_Codes],' +
                'CAST(''<M>'' + REPLACE(REPLACE([Descriptions],''&'',''AND''),'','',''</M><M>'') + ''</M>'' AS XML) AS [XML_Desc] ' +
                'FROM  ##' + @system_name + '_Temp) select * from DCODES' -- you are missing this