Syed Kamran Ahmed Syed Kamran Ahmed - 2 months ago 18
SQL Question

Using stuff in dynamic query string sql

I am trying to fetch two columns with comma separated.This code

SELECT STUFF(( SELECT ',' + Cast(Column1 As varchar(50))+'_'+Cast(Column2 As varchar(50)) FROM Table FOR XML PATH(''), TYPE).value('.', NVARCHAR(MAX)), 1, 1, '') As Result


works for me.But i need to use it in a dynamic query string its getting error when i am trying to do this.

Declare @String AS NVARCHAR(MAX)
Set @string='SELECT STUFF(( SELECT ',' + Cast(Column1 As varchar(50))+'_'+Cast(Column2 As varchar(50)) FROM Table FOR XML PATH(''), TYPE).value('.', NVARCHAR(MAX)), 1, 1, '') As Result'
EXEC sp_executesql @String

Answer

You need to escape the quotes in dynamic SQL by doubling them up, otherwise SQL Server thinks the quote has ended:

Set @string='SELECT STUFF(( SELECT '','' + Cast(Column1 As varchar(50))+''_''...

http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/