How do I provide a string with a list of values to an "IN" statement [duplicate]
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 valu开发者_如何学运维e, 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" />
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.
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, ',')))
This uses the TVF from http://www.eggheadcafe.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
精彩评论