如何生成动态查询的更新查询(自动)? [英] How to generate an update query of a dynamic query (automatically)?
问题描述
我在表列中存储了一些查询,以便稍后通过一些参数执行它们。
但由于特殊字符的原因,将查询格式化为Update语句已经非常令人讨厌了。
例如:
SELECT * FROM MOUNTAINS WHERE MON_NAME ='PALMA'AND MON_DESC LIKE'%TRANVULCANIA%'
$ b $ p然后我需要字符串为udpate查询:$ p $
更新QUERIES
SET QUE_SEL ='SELECT * FROM MOUNTAINS WHERE MON_NAME ='''+'PALMA'+'''AND MON_DESC LIKE'''+'%TRANVULCANIA%'+''''
WHERE QUE_ID = 1
您可以看到第一个'必须替换为<必须将'替换为' 。
这是我正在处理的查询:
$ b $ pre $ DECLARE @QUERY VARCHAR(MAX)
SELECT @ QUERY ='SELECT * FROM QUERIES WHERE QUE_NOMBRE ='''+'PRUEBA 1'+'''
SELECT
trvalue('。','varchar(255)')AS令牌
,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)
这是结果:
$ p $ 令牌id
----------------------------------------------- --- --------------------
SELECT * FROM QUERIES WHERE QUE_NOMBRE = 1
'2
PRUEBA 1 3
'4
5
现在我想要一个列告诉我何时打开什么时候关闭然后我可以设置th e最终替换。
修改@rivarolle提供的解决方案
DECLARE @QUERY VARCHAR(MAX)
DECLARE @FORMATTED varchar(max)
SELECT @ QUERY ='SELECT * FROM QUERIES WHERE QUE_NOMBRE ='' '+'PRUEBA 1'+''''
; WITH TOKENS AS(
SELECT
trvalue('。','varchar(MAX)')AS令牌
,ROW_NUMBER()OVER(ORDER BY(SELECT 1))AS
FROM(
SELECT myxml = CAST('< t> + REPLACE(@QUERY,'''' '< / t>< t>')+'< / t>'AS XML)
)p
交叉应用myxml.nodes '/ t')t(r)
)
,
Tokens2 as(
SELECT
TOKENS.token as token
,quotes .row%2作为tipoapostrofe
从令牌
离开连接(选择row_number()over(按ID asc排序)作为行,a。* FROM(SELECT * from Tokens)a where Token =' ''')引用
on quotes.Id = Tokens.Id
)
SELECT @FORMATTED = STUFF((
SELECT''+ REPLACE(token,'' ''''CASE tipoapostrofe WHEN 1 THEN'''''''''''''''''''''''''''''''''''''''''''') Tokens2
FOR XML PATH('')
),1,1,'')
print @FORMATTED
这个Works只需要一个清理XML特殊字符的函数,另外一个函数用于放回,动态查询打印就可以更新了。
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.
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.
这篇关于如何生成动态查询的更新查询(自动)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!