Way to call SQL stored procedure purely dynamically (dynamic parameters in sp_executesql)?

Question

When calling a SQL Server stored procedure from an external language (c# for example), the manner in which the call is coded is such that a given stored procedure can be entirely described in metadata and called using a generic function. Support for varying numbers of parameters between different procedures is facilitated by a parameters collection on the calling command object.

If one wanted to implement a similar capability entirely within SQL Server, if one uses sp_executesql (a somewhat relevant example of which can be found here: Dynamic Search Conditions in T‑SQL)....you can get most of the way 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 within @sql, the parameters list is stored within @paramList, and what follows is a list of the actual parameters.

Both @sql and @paramList are simple nvarchar variables that can be set programmatically (by reading from metadata and assigning to variables), but the actual parameters themselves are hardcoded.

So, the question is:

Is there a way to specify the actual parameters & values such that the implementation of this entire function could be entirely generic?

Vitaly Borisov · Answer 1 · 2015-02-18T02:17:39.620

The list of parameters can be sent as comma-delimited (or special character delimited) list, the list can be parsed into the table then. In this example I used "," and "=".

This one was my initial 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

The smarter one:

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)

The best one is to send XML with list of parameters and then parse this XML to the table.

Please let me know if you have any questions.

Update: So here you need to provide only one value - list of parameters and their values. Inside the query 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

But this doesn;t solve the problem of calling EXEC sp_executesql without having to explicitly pass each parameter value, or am I missing something? — tbone, Feb 15 '15 at 10:34
Or maybe I'm missing the point. Could you please provide more specific example what you're trying to send to SQL and what should happen next, i.e. how values need to be processed? — Vitaly Borisov, Feb 18 '15 at 02:26
Follow on question: http://stackoverflow.com/questions/28885415/performance-differences-between-different-ways-of-calling-sp-executesql-with-bo — tbone, Mar 05 '15 at 21:42

score 0 · Accepted Answer · edited May 23 '17 at 11:43

It can be done, but I do not yet know if there are performance implications, and some approaches are open to sql injection.

Some examples are shown here in a secondary question asking specifically about performance using the different syntaxes (some of which are conducive to purely dynamic SQL, others which are not):

Performance differences calling sp_executesql with dynamic SQL vs parameters

Way to call SQL stored procedure purely dynamically (dynamic parameters in sp_executesql)?

2 Answers2