I have a report that use a multi-value parameter into a "in" statement in a query. For example (with @areas as the multi-value parameter):
我有一个报告,它在查询的“in”语句中使用多值参数。例如(使用@areas作为多值参数):
select * from regions where areas in (@areas)
It works perfectly but now I need to send the same parameter to a function in the SQL Server 2005 database:
它工作得很好,但现在我需要将相同的参数发送到SQL Server 2005数据库中的函数:
select name, myFunction(@areas) from regions where areas in (@areas)
The @areas parameter in the function are going to be used in a "in" statement as well. I tried to receive it with a varchar parameter but this causes an error. When I use the SQL Profiler, I see that the parameter is passed in this format:
函数中的@areas参数也将用于“in”语句中。我尝试使用varchar参数接收它,但这会导致错误。当我使用SQL事件探查器时,我看到该参数以这种格式传递:
N''1'',N''2'',N''3''
The specific questions here are, what data type the function parameter @areas must be? And how can I use that parameter into a "in" statement in the function?
这里的具体问题是,函数参数@areas必须是什么数据类型?如何将该参数用于函数中的“in”语句?
Thanks
3 个解决方案
#1
Generally if you are going to be passing in a list of some type as a parameter, you are going to want to pass it in as a varchar of a large enough length to handle the entirety of it. You could also pass it in as an XML parameter, but I've always preferred using the varchar route and parsing it that way.
通常,如果您要将某个类型的列表作为参数传递,那么您将要将其作为一个足够大的varchar传递给它来处理它的全部内容。您也可以将其作为XML参数传递,但我总是首选使用varchar路由并以此方式解析它。
#2
For sql 2008 there are table valued params as mentioned. Otherwise your only options are to pass it as a packed value (either comma separated variety in varchar or xml), or else populate a temp table and have the function read from the temp table. If you are calling multiple functions with the same large input, the temp table is likely the best route so you don't have to keep parsing it. If the native input is xml (i.e. you have some input from an external partner like facebook) then it's usually best to keep it as xml. If it is just a modest list of ints then a list of comma separated values isn't bad.
对于sql 2008,如上所述有表值params。否则,您唯一的选择是将其作为打包值(varchar或xml中的逗号分隔变量)传递,或者填充临时表并从临时表中读取函数。如果您使用相同的大输入调用多个函数,则临时表可能是最佳路径,因此您不必继续解析它。如果原生输入是xml(即你有一些像facebook这样的外部合作伙伴的输入),那么通常最好将它保持为xml。如果它只是一个适度的整数列表,那么逗号分隔值的列表也不错。
#3
Table valued parameters are supported in SQL Server starting version 2008: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
SQL Server 2008版本支持表值参数:http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
#1
Generally if you are going to be passing in a list of some type as a parameter, you are going to want to pass it in as a varchar of a large enough length to handle the entirety of it. You could also pass it in as an XML parameter, but I've always preferred using the varchar route and parsing it that way.
通常,如果您要将某个类型的列表作为参数传递,那么您将要将其作为一个足够大的varchar传递给它来处理它的全部内容。您也可以将其作为XML参数传递,但我总是首选使用varchar路由并以此方式解析它。
#2
For sql 2008 there are table valued params as mentioned. Otherwise your only options are to pass it as a packed value (either comma separated variety in varchar or xml), or else populate a temp table and have the function read from the temp table. If you are calling multiple functions with the same large input, the temp table is likely the best route so you don't have to keep parsing it. If the native input is xml (i.e. you have some input from an external partner like facebook) then it's usually best to keep it as xml. If it is just a modest list of ints then a list of comma separated values isn't bad.
对于sql 2008,如上所述有表值params。否则,您唯一的选择是将其作为打包值(varchar或xml中的逗号分隔变量)传递,或者填充临时表并从临时表中读取函数。如果您使用相同的大输入调用多个函数,则临时表可能是最佳路径,因此您不必继续解析它。如果原生输入是xml(即你有一些像facebook这样的外部合作伙伴的输入),那么通常最好将它保持为xml。如果它只是一个适度的整数列表,那么逗号分隔值的列表也不错。
#3
Table valued parameters are supported in SQL Server starting version 2008: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
SQL Server 2008版本支持表值参数:http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters