How to generate an update query of a dynamic query

Question

I'm storing some queries in a table column so I can execute them later passing some parameters. But it has been really annoying to format the query into an Update sentence, because of the special characters.

For Example:

SELECT * FROM MOUNTAINS WHERE MON_NAME='PALMA' AND MON_DESC LIKE '%TRANVULCANIA%'

Then I need the string just for the udpate query:

UPDATE QUERIES 
SET QUE_SEL='SELECT * FROM MOUNTAINS WHERE MON_NAME='''+'PALMA'+''' AND MON_DESC LIKE '''+'%TRANVULCANIA%'+''' '
WHERE QUE_ID=1

as you can see the first ' must be replaced for '''+' but the next door ' must be replaced by '+'''

This is the query I'm working on:

DECLARE @QUERY VARCHAR(MAX)

SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''' '

SELECT 
      t.r.value('.', 'varchar(255)') AS token
    , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM (
    SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
        ) p
        CROSS APPLY myxml.nodes('/t') t(r)

this is the result:

token                                              id
-------------------------------------------------- --------------------
SELECT * FROM QUERIES WHERE QUE_NOMBRE=            1
'                                                  2
PRUEBA 1                                           3
'                                                  4
                                                   5

Now I want a column that tell me when to open and when to close and then I can set the final replace.

Answer 1

Adapting the solution given by @rivarolle

DECLARE @QUERY VARCHAR(MAX)
DECLARE @FORMATTED varchar(max)

SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''''

;WITH TOKENS AS(
SELECT 
      t.r.value('.', 'varchar(MAX)') AS token
      , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id 
FROM (
        SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
            ) p
            CROSS APPLY myxml.nodes('/t') t(r)
    ) 
    ,

Tokens2 as (
        SELECT 
        TOKENS.token as token
        ,quotes.row%2 as tipoapostrofe
from Tokens 
left join (select row_number() over( order by Id asc) as row, a.* FROM (SELECT * from Tokens) a where Token = '''') quotes 
    on quotes.Id = Tokens.Id
)

SELECT @FORMATTED = STUFF((
    SELECT ' ' + REPLACE(token,'''',CASE tipoapostrofe WHEN 1 THEN '''''''+''' WHEN 0 THEN '''+''''''' ELSE '' END) AS [text()]
    FROM Tokens2
FOR XML PATH('')
    ), 1, 1, '')
print @FORMATTED

This Works, just need a function for cleaning XML special characters and another for putting back, and the Dynamic queries are printed ready for an update.

Answer 2

I think its not necessary to replace an apostrophe with '''+' to open and '+''' to close, I made some probes and you can exec a query that you replace opening and closing apostrophes with the same.. for example '''+' for open and '''+' for close.

So the query would be:

DECLARE @QUERY VARCHAR(MAX)
DECLARE @FORMATTED varchar(max)

SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''''

SELECT @FORMATTED= STUFF((
    SELECT ' ' +
         (SELECT 
            CASE 
                WHEN t.r.value('.', 'varchar(250)')='''' THEN REPLACE(t.r.value('.', 'varchar(250)'), '''','''''''+''')
                ELSE t.r.value('.', 'varchar(250)')
            END
         ) AS [text()] 
--      , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
    FROM (
        SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
            ) p
            CROSS APPLY myxml.nodes('/t') t(r)
FOR XML PATH('')
), 1, 1, '')

SET @FORMATTED=REPLACE(@FORMATTED,'&#x20;','')
PRINT @FORMATTED

then I get:

SELECT * FROM QUERIES WHERE QUE_NOMBRE= '''+' PRUEBA 1 '''+'

then I copy into a variable and execute

DECLARE @VAR VARCHAR(500)
SET @VAR='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'''+' '
EXEC(@VAR)

It Works for very simple queries, but with longer and complicated queries it doesn't works..

Answer 3

Assuming your token table is Tokens(Token, Id, Position):

update Tokens
     set position = quotes.row%2
from Tokens 
left join (select row_number() over( order by Id asc) as row, a.* FROM (SELECT * from Tokens) a where Token = '''') quotes 
    on quotes.Id = Tokens.Id

The position column will have a value of 1 for starting quote and 0 for closing quote. NULL for the rest.