为一个SQL参数传递多个值 [英] Passing multiple values for one SQL parameter

问题描述

我有一个CheckBoxList，用户可以从中选择多个项目。然后，我需要能够将这些值传递给我的存储过程，以便可以在以下条件下使用它们：

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)

我试图这样做，因此它的nvarchar参数和i将字符串 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)

但这返回以下错误：

Conversion failed when converting the nvarchar value '1,2,3' to data type int

时，转换失败

任何帮助将不胜感激！

Any help would be much appreciated!

推荐答案

有几种方法可以做到这一点。
您可以像下面的示例一样将参数作为XML Blob传递：

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>'

或将值作为CSV传递，并使用split函数将值分离出来放入表变量中（那里有很多拆分函数，快速搜索会抛出一个错误）。

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'

如果如果您使用的是SQL 2008或更高版本，则可以使用表值参数，该参数允许您将TABLE变量作为参数传递。我博客关于这3个问题不久前便有了性能比较。

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.

这篇关于为一个SQL参数传递多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！