如何为字符串提供带有"IN"值列表的字符串陈述 [英] How do I provide a string with a list of values to an "IN" statement

问题描述

我通过遍历CheckBoxList中的选择来创建一个字符串，该字符串是逗号分隔值的列表.我能够显示此值，所以我知道它正在创建我期望的值.我正在尝试将此列表传递给SELECT查询中的IN语句:

I am creating a string that is a list of comma-delimitted values by looping through the selections in a CheckBoxList. I am able to display this value, so I know that it is creating what I expect. I am attempting to pass this list to an IN statment in a SELECT query:

SelectCommand="SELECT ThisDate, DATEPART(dw, ThisDate) AS Expr1 FROM fbCalendar WHERE (ThisDate >= @ThisDate) AND (ThisDate <= @ThisDate2) AND (DATEPART(dw, ThisDate) IN (@TheseDays))"

<asp:ControlParameter ControlID="Label1" Name="TheseDays" PropertyName="Text" Type="String" />  

只要仅选择一个项目，此方法就可以正常工作，但是选择第二个项目失败并显示以下消息:将nvarchar值'4,5'转换为数据类型int时，转换失败.

This works fine as long as there is only a single item selected, but selecting a second item fails with the message: Conversion failed when converting the nvarchar value '4,5' to data type int.

但是，我不知道何时将其转换为INT.我为SELECT查询尝试了许多不同的格式化尝试(例如将字符串封装在括号中(例如(4,5)")，但是我还没有找到合适的方法来使这项工作.问题，但也许我还缺少其他东西.

However, I do not understand when this would be converted to an INT. I have tried many different formatting attempts (such as encapsulating the string in parenthesis (e.g. "(4,5)" ) for the SELECT query, but I have yet to find the right one to make this work. It seems like formatting is the problem, but perhaps I am missing something else.

推荐答案

Degan.

我创建了一个表来存储要传递给查询的值.它只是似乎不正确.

I created a table to store the values to pass to the query. It just doesn't seem right.

您不必创建实际的表.您可以使用表值函数，并在查询中使用它，例如如下.

You don't have to create an actual table. You can use a Table Valued Function and use that in your query e.g. as below.

(DATEPART(dw, ThisDate) IN (SELECT [item] FROM dbo.fnSplit(@TheseDays, ',')))

这使用来自 http://www.eggheadcafe的TVF.com/community/aspnet/13/10021854/fnsplit.aspx ).

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

这篇关于如何为字符串提供带有"IN"值列表的字符串陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！