为一个 SQL 参数传递多个值
我有一个 CheckBoxList,用户可以从列表中选择多个项目。然后,我需要能够将这些值传递给我的存储过程,以便它们可以在 WHERE 条件中使用,例如:
WHERE ID IN (1,2,3)
我尝试这样做,以便它是一个 nvarchar 参数,并且我传递字符串 1,2,3 with:
WHERE ID IN (@IDs)
但这返回了以下错误:
Conversion failed when converting the nvarchar value '1,2,3' to data type int
任何帮助将不胜感激!
I have a CheckBoxList where users can select multiple items from the list. I then need to be able to pass these values to my Stored Procedure so they can be used in a WHERE condition like:
WHERE ID IN (1,2,3)
I tried doing this so that its a nvarchar parameter and i pass the string 1,2,3
with:
WHERE ID IN (@IDs)
But this returned the following error:
Conversion failed when converting the nvarchar value '1,2,3' to data type int
Any help would be much appreciated!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
有几种方法可以做到这一点。
您可以将参数作为 XML blob 传递,如下例所示:
或者将值作为 CSV 传递,并使用 split 函数将值拆分到表变量中(那里有很多 split 函数,快速搜索会抛出一个)向上)。
如果您使用的是 SQL 2008 或更高版本,则可以使用表值参数,它允许您将 TABLE 变量作为参数传递。我博客关于这3个不久前,我们进行了快速的性能比较。
There's a few ways of doing it.
You could pass in the parameter as an XML blob like this example:
Or pass in the values as CSV and use a split function to split the values out into a table variable (there's a lot of split functions out there, quick search will throw one up).
If you were using SQL 2008 or later, you could have used Table Valued Parameters which allow you to pass a TABLE variable in as a parameter. I blogged about these 3 approaches a while back, with a quick performance comparison.
我确实找到了类似问题的解决方案。
它用于数据驱动的订阅,但可以轻松更改以在参数中使用。
查看我的博客文章并附有详细说明
如果您在转换时遇到问题对于存储过程调用,请告诉我。
I did find a solution for a similar problem.
It is used for a data driven subscription, but can be easily altered for use in a parameter.
check my blog post here with a detailed description
If you are having problem converting it to a stored procedure call, just let me know.