Execute Stuff expression in Dynamic SQL with lots of quotes

1

I have this Stuff expression:

select stuff((SELECT ','''+ITEMID+''''
              FROM INVENTTABLE 
              WHERE WK_ITEMSOLVED = 1 and ITEMTYPE = 1
              for xml path(''),type).value('.', 'nvarchar(max)'), 1, 1, '')

that returns: (every Item is wrapped in quotes)

'ItemXX','ItemYY','ItemZZ'

Now I need to place this expression inside dynamical SQL. But I can't manage to code those quotes that embed the ITEMID correctly.

declare @sql nvarchar(max)
set @sql = 'select stuff((SELECT '',''+ITEMID+'''' 
            from INVENTTABLE 
            WHERE WK_ITEMSOLVED = 1 and  ITEMTYPE = 1
            for xml path(''''),type).value(''.'', ''nvarchar(max)''), 1, 1, '''')'
exec sp_executesql @sql

This is not working - nor does any other attempt. The error it throws alters between 'unclosed quotes' and 'invalid column name' according to the amount of quotes I place

2

I solved it empirically:

set @sql = 'select stuff((SELECT '','''''' + ITEMID + ''''''''
            from INVENTTABLE 
            WHERE WK_ITEMSOLVED = 0 and ITEMTYPE =1
            for xml path(''''),type).value(''.'', ''nvarchar(max)''), 1, 1, '''')'
3

I find that in some circumstances, where the number of doubled-up-doubled-up!! quotes gets ridiculous, that it is easier to build the SQL in steps, and then when the result from the first step is used within the second step (and, yet again, needs the quotes doubled up) I use REPLACE to do the doubling-up. Thus I only have to have doubled-up quotes at Level 1 of any code that I write.

For example (this is runable to see what outputs at DEBUG1 and DEBUG2)

DECLARE	@strSQL varchar(8000),
	@Param1 varchar(100) = 'xxx''yyy'	-- Test data!!

SELECT @strSQL =
'SELECT Col1, Col2, ...,
        [ChangeDate]=NullIf([ChangeDate], ''19000101''),
FROM MyTable
WHERE SomeColumn >= ''' + COALESCE(REPLACE(@Param1, '''', ''''''),' ') + '''
'

-- Debug:
SELECT @strSQL AS [Debug1]

SELECT @strSQL =
'INSERT INTO #MyTempTable
SELECT	*
FROM OPENQUERY([LinkedServer], '''
	+ REPLACE(@strSQL, '''', '''''')
	+ ''')'

-- Debug: -- Doubled-up & Doubled-up again!!
SELECT @strSQL AS [Debug2]
1 Like