Is it possible to write a stored procedure (in tsql or other variants) with dynamic parameters like sp_executesql?
是否可以使用sp_executesql等动态参数编写存储过程(在tsql或其他变体中)?
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.
即,事先未知的参数的数量和类型,应该在客户端动态构建。只是将任何类型的任意数量的参数传递给sp_executesql的方式。
7 个解决方案
#1
1
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.
你可以将数组作为字符串传递给一些分隔符,如val1 $ datatype1 $; val2 $ datatype2 ...使用循环来构建字符串。有点老派但在客户端和服务器端很容易做到。
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) .
一块XML会更好,你可以使用XML函数在服务器上对它进行转换(对我来说它们有点麻烦)。
If you have more complex things to do with the values, you may consider using server side .NET procedures.
如果您对值有更复杂的处理,可以考虑使用服务器端.NET过程。
#2
0
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...
您可能还有一长串可选的INPUT参数列表......
Is there some compelling reason you don't want to use sp_executesql?
是否有一些令人信服的理由你不想使用sp_executesql?
#3
0
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.
我可以使用sp_executesql。问题是它不是1,2,3简单的更新。这就像将一个长存储过程传递给sp_executesql。鉴于小的变化,它会产生性能问题。
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.
我想使用存储过程并多次调用带有参数的sp_executesql。至少静态部分是编译的,总体而言,它比动态构建整个存储过程更快。
to summarize performance is the main concern
总结性能是主要关注点
#4
0
to clarify further, it will be similar to this in C# :
进一步澄清,它将与C#中的类似:
void StoredProcedure(params object[] dynamicFields)
void StoredProcedure(params object [] dynamicFields)
{
// begin tran
//开始tran
//...some work here
//...some在这里工作
sp_executesql(SQL11, dynamicFields);
//...some work here
//...some在这里工作
sp_executesql(SQL22, dynamicFields);
//...some work here
//...some在这里工作
sp_executesql(SQL33, dynamicFields);
//...some work here
//...some在这里工作
// commit tran
// commit tran
}
you can call it with any number or type of params or think C++ var args
你可以使用任何数量或类型的参数来调用它或者认为C ++ var args
#5
0
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.
看看这个问题中接受的答案。这里建议我使用MS SQL的内置XML函数来构造动态where子句。
To clarify: I am suggesting you wrap your dynamic parameters in XML.
澄清一下:我建议你用XML包装你的动态参数。
#6
0
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.
类型和数量未知?你可以在数组的各个参数和.Length上使用.getType()来获得它。从中构建XML片段并将其作为一个参数推送到只有一个xml输入参数的SP中。 .getType()也适用于普通对象(),如果它们被分配了类型对象。
#7
0
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)
是的,始终可以构建整个sql字符串并将其传递给存储过程。但是这样你就无法获得sql paramaterization的性能和安全性好处。 sp_executesql的全部原因优先于adhoc sql。如果我没有任何其他选择,我将不得不做那样的事情(尽管有点不同)
#1
1
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.
你可以将数组作为字符串传递给一些分隔符,如val1 $ datatype1 $; val2 $ datatype2 ...使用循环来构建字符串。有点老派但在客户端和服务器端很容易做到。
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) .
一块XML会更好,你可以使用XML函数在服务器上对它进行转换(对我来说它们有点麻烦)。
If you have more complex things to do with the values, you may consider using server side .NET procedures.
如果您对值有更复杂的处理,可以考虑使用服务器端.NET过程。
#2
0
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...
您可能还有一长串可选的INPUT参数列表......
Is there some compelling reason you don't want to use sp_executesql?
是否有一些令人信服的理由你不想使用sp_executesql?
#3
0
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.
我可以使用sp_executesql。问题是它不是1,2,3简单的更新。这就像将一个长存储过程传递给sp_executesql。鉴于小的变化,它会产生性能问题。
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.
我想使用存储过程并多次调用带有参数的sp_executesql。至少静态部分是编译的,总体而言,它比动态构建整个存储过程更快。
to summarize performance is the main concern
总结性能是主要关注点
#4
0
to clarify further, it will be similar to this in C# :
进一步澄清,它将与C#中的类似:
void StoredProcedure(params object[] dynamicFields)
void StoredProcedure(params object [] dynamicFields)
{
// begin tran
//开始tran
//...some work here
//...some在这里工作
sp_executesql(SQL11, dynamicFields);
//...some work here
//...some在这里工作
sp_executesql(SQL22, dynamicFields);
//...some work here
//...some在这里工作
sp_executesql(SQL33, dynamicFields);
//...some work here
//...some在这里工作
// commit tran
// commit tran
}
you can call it with any number or type of params or think C++ var args
你可以使用任何数量或类型的参数来调用它或者认为C ++ var args
#5
0
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.
看看这个问题中接受的答案。这里建议我使用MS SQL的内置XML函数来构造动态where子句。
To clarify: I am suggesting you wrap your dynamic parameters in XML.
澄清一下:我建议你用XML包装你的动态参数。
#6
0
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.
类型和数量未知?你可以在数组的各个参数和.Length上使用.getType()来获得它。从中构建XML片段并将其作为一个参数推送到只有一个xml输入参数的SP中。 .getType()也适用于普通对象(),如果它们被分配了类型对象。
#7
0
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)
是的,始终可以构建整个sql字符串并将其传递给存储过程。但是这样你就无法获得sql paramaterization的性能和安全性好处。 sp_executesql的全部原因优先于adhoc sql。如果我没有任何其他选择,我将不得不做那样的事情(尽管有点不同)