为一个 SQL 参数传递多个值

发布于 2024-10-10 19:21:09 字数 398 浏览 4 评论 0原文

我有一个 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

压抑⊿情绪 2024-10-17 19:21:09

有几种方法可以做到这一点。
您可以将参数作为 XML blob 传递,如下例所示:

CREATE PROCEDURE [dbo].[uspGetCustomersXML]
    @CustomerIDs XML
AS
BEGIN
SELECT c.ID, c.Name
FROM [dbo].[Customer] c
JOIN @CustomerIDs.nodes('IDList/ID') AS x(Item) ON c.ID = Item.value('.', 'int' )
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersXML] '<IDList><ID>1</ID><ID>10</ID><ID>100</ID></IDList>'

或者将值作为 CSV 传递,并使用 split 函数将值拆分到表变量中(那里有很多 split 函数,快速搜索会抛出一个)向上)。

CREATE PROCEDURE [dbo].[uspGetCustomersCSV]
    @CustomerIDs VARCHAR(8000)
AS
BEGIN
SELECT c.Id, c.Name
FROM [dbo].[Customer] c
JOIN dbo.fnSplit(@CustomerIDs, ',') t ON c.Id = t.item
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersCSV] '1,10,100'

如果您使用的是 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:

CREATE PROCEDURE [dbo].[uspGetCustomersXML]
    @CustomerIDs XML
AS
BEGIN
SELECT c.ID, c.Name
FROM [dbo].[Customer] c
JOIN @CustomerIDs.nodes('IDList/ID') AS x(Item) ON c.ID = Item.value('.', 'int' )
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersXML] '<IDList><ID>1</ID><ID>10</ID><ID>100</ID></IDList>'

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).

CREATE PROCEDURE [dbo].[uspGetCustomersCSV]
    @CustomerIDs VARCHAR(8000)
AS
BEGIN
SELECT c.Id, c.Name
FROM [dbo].[Customer] c
JOIN dbo.fnSplit(@CustomerIDs, ',') t ON c.Id = t.item
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersCSV] '1,10,100'

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.

愁以何悠 2024-10-17 19:21:09
alter procedure c2
(@i varchar(5))
as
begin
    declare @sq nvarchar(4000)
    set @sq= 'select * from test where id in (<has_i>) '
    SET @sq= REPLACE(@sq, '<has_i>', @i)
    EXECUTE sp_executesql  @sq
end

exec c2 '1,3'
alter procedure c2
(@i varchar(5))
as
begin
    declare @sq nvarchar(4000)
    set @sq= 'select * from test where id in (<has_i>) '
    SET @sq= REPLACE(@sq, '<has_i>', @i)
    EXECUTE sp_executesql  @sq
end

exec c2 '1,3'
放肆 2024-10-17 19:21:09

我确实找到了类似问题的解决方案。
它用于数据驱动的订阅,但可以轻松更改以在参数中使用。
查看我的博客文章并附有详细说明

如果您在转换时遇到问题对于存储过程调用,请告诉我。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文