sql-server - 纯动态调用SQL存储过程的方法(sp_executesql中的动态参数)？

Question

当从外部语言(例如C#)调用SQL Server存储过程时，调用的编码方式是这样的:给定的存储过程可以在元数据中完整描述，并可以使用泛型函数进行调用。通过调用命令对象上的参数集合，可以促进对不同过程之间不同数量参数的支持。

如果一个人想完全在SQL Server中实现类似的功能，则使用 sp_executesql (可以在此处找到一个与之相关的示例:Dynamic Search Conditions in T‑SQL)....您可以找到大部分的方式，但是主要的问题是函数调用中的参数必须经过硬编码。

文章中的示例:

EXEC sp_executesql @sql, @paramlist,                               
               @orderid, @fromdate, @todate, @minprice,        
               @maxprice,  @custid, @custname, @city, @region, 
               @country, @prodid, @prodname   

在此示例中，SQL语句存储在 @sql内，参数列表存储在 @paramList内，其后是实际参数的列表。
@sql和 @paramList都是简单的 nvarchar变量，可以通过编程(通过从元数据读取并分配给变量)进行设置，但是实际参数本身是硬编码的。

因此，问题是:

有没有一种方法可以指定实际的参数和值，以使整个功能的实现完全通用？

Answer 1

参数列表可以作为逗号分隔(或特殊字符分隔)列表发送，然后可以将该列表解析到表中。
在此示例中，我使用了“，”和“=“。

这是我最初的解决方案:

DECLARE @List VARCHAR(MAX) = 'a=1,b=3,c=hey,d=12/05/10,val5='
DECLARE @Delimiter1 VARCHAR(1) = ','
DECLARE @Delimiter2 VARCHAR(1) = '='
----
SELECT y.i.value('(./text())[1]', 'nvarchar(4000)') pass1
INTO #Buffer
FROM ( 
    SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(@List, @Delimiter1, '</i><i>') 
        + '</i>').query('.')
) a 
CROSS APPLY x.nodes('i') y(i)

SELECT ROW_NUMBER()OVER(ORDER BY(SELECT 1)) rn,y.i.value('(./text())[1]', 'nvarchar(4000)') pass2
INTO #PreResult
FROM ( 
    SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(b.pass1, @Delimiter2, '</i><i>') 
        + '</i>').query('.')
    FROM #Buffer b
    WHERE b.pass1 LIKE '%=%' AND b.pass1 NOT LIKE '%=%=%' -- to make sure assignment has place and there is no double or more assignments
) a 
CROSS APPLY x.nodes('i') y(i)

SELECT @List '@List'
--SELECT '' '#Buffer',* FROM #Buffer b
--SELECT '' '#PreResult',* FROM #PreResult p

SELECT p.pass2 [Variable],p2.pass2 [Value]
FROM #PreResult p
INNER JOIN #PreResult p2 ON p2.rn = p.rn + 1
WHERE p.rn%2 > 0

DROP TABLE #Buffer
DROP TABLE #PreResult

聪明一点:

DECLARE @List VARCHAR(MAX) = 'a=1,b=3,c=hey,d=12/05/10,val5='

DECLARE @Delimiter1 VARCHAR(1) = ','
DECLARE @Delimiter2 VARCHAR(1) = '='

SELECT v.v.value('(./text())[1]', 'nvarchar(4000)') [Variable],n.n.value('(./text())[1]', 'nvarchar(4000)') [Value]
FROM ( 
    SELECT x = CONVERT(XML, 
        '<a><v>' + REPLACE(REPLACE(@List,@Delimiter1,'</n></a><a><v>'),@Delimiter2,'</v><n>') + '</n></a>'
    ).query('.')
) a 
CROSS APPLY x.nodes('a') y(a)
CROSS APPLY y.a.nodes('v') v(v)
CROSS APPLY y.a.nodes('n') n(n)

最好的方法是发送带有参数列表的XML，然后将此XML解析到表中。

请让我知道，如果你有任何问题。

更新:
因此，这里您只需要提供一个值-参数及其值的列表。
在查询中，您可以对它们执行任何操作。

DECLARE @sql NVARCHAR(MAX),@paramlist NVARCHAR(MAX)

SET @sql = N'
DECLARE @Delimiter1 VARCHAR(1) = '',''
DECLARE @Delimiter2 VARCHAR(1) = ''=''

SELECT v.v.value(''(./text())[1]'', ''NVARCHAR(4000)'') [Variable],n.n.value(''(./text())[1]'', ''NVARCHAR(4000)'') [Value]
INTO #Values
FROM ( 
    SELECT x = CONVERT(XML, 
        ''<a><v>'' + REPLACE(REPLACE(@List,@Delimiter1,''</n></a><a><v>''),@Delimiter2,''</v><n>'') + ''</n></a>''
    ).query(''.'')
) a 
CROSS APPLY x.nodes(''a'') y(a)
CROSS APPLY y.a.nodes(''v'') v(v)
CROSS APPLY y.a.nodes(''n'') n(n)

/*Do whatever you want with the values*/
/*There even could be a stored proc call based on parameters provided*/
SELECT v.Value FROM #Values v WHERE v.Variable = ''c''


DROP TABLE #Values
'
SET @paramlist = '@list nvarchar(max)'

DECLARE @List VARCHAR(MAX) = 'a=1,b=3,c=hey,d=12/05/10,val5='

EXEC sp_executesql @sql, @paramlist, @list=@List