How to call a SQL stored procedure dynamically (dynamic parameters in sp_executesql)?
When calling a SQL Server stored procedure from an external language (such as C #), the way to code the call is such that the stored procedure can be fully described in metadata and called using a common function. Maintaining a different number of parameters between different procedures is facilitated by collecting the parameters in the caller of the command.
If someone wanted to implement a similar capability entirely in SQL Server if you are using sp_executesql
(a somewhat relevant example might be here: Dynamic Search Conditions in T-SQL ) .... you can get most of the path there, but the main problem is that the parameters in the function call must be hardcoded.
Example from the article:
EXEC sp_executesql @sql, @paramlist,
@orderid, @fromdate, @todate, @minprice,
@maxprice, @custid, @custname, @city, @region,
@country, @prodid, @prodname
In this example, the SQL statement is stored in @sql
, the parameter list is stored in @paramList
, and below is the actual parameter list.
Both @sql
and @paramList
are simple variables nvarchar
that can be set by software (by reading from the metadata and assign variables), but the actual parameters are hard-coded.
So the question is:
Is there a way to specify the actual parameters and values so that the implementation of this whole function can be completely generic?
source to share
It can be done, but I don't know yet if there are performance implications and some approaches are open to SQL injection.
Some examples are shown here in a secondary question that specifically asks the question of performance using different syntaxes (some of which favor purely dynamic SQL, while others do not):
Performance differences causing sp_executesql with dynamic SQL vs parameters
source to share
The parameter list can be sent as a comma delimited list (or a special character delimiter), then the list can be parsed into a table. In this example I used "," and "=".
This was my original solution:
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
Smarter:
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)
Your best bet is to send an XML with a list of parameters and then parse that XML into a table.
Please let me know if you have any questions.
Update : So here you only need to provide one value - a list of parameters and their values. Within the request, you can do whatever you want with them.
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
source to share