Is it possible to write a stored procedure with dynamic parameters like sp_executesql?

Question

Is it possible to write a stored procedure (in tsql or other variants) with dynamic parameters like sp_executesql?

i.e. the number and type of parameters in not known in advance and should be built dynamically on client side. just the way you can pass any number of parameters with any type to sp_executesql.

Answer 1

I suppose you could, but you'd have to provide a parser on the DB server side to interpret those parameters and values. You would also have to either forego the benefits of runtime compilation, or do that yourself somehow as well.

You'd also probably have an awfully long list of optional INPUT parameters...

Is there some compelling reason you don't want to use sp_executesql?

Answer 2

I use custom fields. type and number unknown.

I can use sp_executesql. the problem is it is not 1,2,3 simple updates. It is like passing a long stored procedure to sp_executesql. given the small variations it creates performance problems.

I would like to use a stored procedure and call sp_executesql with the params multiple times. at least the static parts are compiled and overall it will be faster than building the whole stored procedure dynamically.

to summarize performance is the main concern

Answer 3

to clarify further, it will be similar to this in C# :

void StoredProcedure(params object[] dynamicFields)

{

// begin tran

//...some work here

sp_executesql(SQL11, dynamicFields);

//...some work here

sp_executesql(SQL22, dynamicFields);

//...some work here

sp_executesql(SQL33, dynamicFields);

//...some work here

// commit tran

}

you can call it with any number or type of params or think C++ var args

Answer 4

you could pass the array as a string with some delimiters like val1$datatype1$;val2$datatype2... use a loop to build the string. a bit old school but easy to do on client and server side.

a piece of XML would be even better, you could transform it on the server with the XML functions (they are a bit cumbersome, to me) .

If you have more complex things to do with the values, you may consider using server side .NET procedures.

Answer 5

Take a look at the accepted answer in this question. Here it is suggested that I use MS SQL's built-in XML functions to construct a dynamic where-clause.

To clarify: I am suggesting you wrap your dynamic parameters in XML.

Answer 6

type and number unknown ? you can use .getType() on the individual params and .Length of the array to get this. Build a XML fragment from it and push it as one parameter into a SP with only one xml input param. .getType() works on plain object()s too, if they were assigned typed objects.

Answer 7

Yes it is always possible to build the whole sql string and pass it to stored procedure. But that way you do not get the performance and security benefits of sql paramaterization. The whole reason sp_executesql is prefered over adhoc sql. If I do not have any other choice I will have to do something like that (a little bit different though)