你不能直接将逗号分隔的值传递给T-SQL函数！您需要使用存储过程 [ ^ ]你在其中'将调用 SplitDelimiterString1 函数。



如何调用存储过程？请参阅：如何：使用Visual C＃.NET在ASP.NET中调用SQL Server存储过程 [ ^ ]



如何选中复选框到字符串变量？ http://***.com/questions/ 3655068 /如何获取最新选择的价值来自复选框列表 [ ^ ]



备用拆分功能：在SQL IN中使用逗号分隔值参数字符串条款 [ ^ ]

I have a checkbox list and a many to many table. i am trying to use the checkbox list to filter a sql query and the function works. the query works, but when i try to hook up the html checkbox list to the query..it falls apart.

i''ve been trying to use the sqldatasource wizard to plug everything together. i set the checkbox list as the control parameter, but for some reason, the query doesn''t work properly when i try to debug the page.

how do i send the checkbox selected values to the split function? is it possible to do this with the asp sqldatasource wizard? if not...how do i accomplish this?

here''s the select parameter:

<asp:ControlParameter ControlID="DropDownCheckBoxes1"

                        ConvertEmptyStringToNull="False" DefaultValue="0" Name="Ids"

                        PropertyName="SelectedValue" Size="8000" Type="String" />

here''s the sql function:

ALTER FUNCTION SplitDelimiterString1 (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

RETURNS @ItemTable TABLE (Item VARCHAR(8000))

AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);

    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 
    
    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 
        
        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            
        
        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)
        
        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END
     
    RETURN
END

here''s the sql query

SELECT DISTINCT 
                         person.personID, sports.sportsID, person.personName, sports.sport,
 
FROM            sports INNER JOIN
                person INNER JOIN
                personSport ON personSport.personID = person.personID 
                       INNER JOIN
                personSport ON personSport.sportID = sports.sportsID
 
WHERE        (person.personName LIKE '%' + @personName + '%') 
              AND 
             (sports.sportID IN
                    (SELECT   ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)

You can''t pass comma separated values into T-SQL function directly! You need to use stored procedure[^] in which you''ll call SplitDelimiterString1 function.

How to call stored procedure? See this: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET[^]

How to get selected checkboxes into string variable? http://***.com/questions/3655068/how-to-get-the-latest-selected-value-from-a-checkbox-list[^]

Alternative split function: Using comma separated value parameter strings in SQL IN clauses[^]