Passing a SQL parameter to an IN() clause using typed datasets in .NET

Question

First apologies as there are similar questions on this site, but none of them answer this problem directly.

Im using typed datasets in VS 2010. I create a TableAdapter in a Dataset with a query like:

SELECT * from Table WHERE ID IN(@IDs)

Now if I call: TableAdapter.Fill(MyDataTable,"1,2,3") an error occurs stating that VS cannot convert 1,2,3 to type int. Fair enough.

So then i decide to change the Parameter (i.e. @IDs) type to string in the Parameter collection. Try again - still the same error message.

So is there any way this typed dataset can accept my "1,2,3" parameter? At the moment i only have a few parameters to pass, so i could easily just create 5 or so parameters and pass them separately, but what if there are hundreds? Is there any way I can call the Fill() method with my comma separated parameter?

(i know i can use Dynamic SQL to create the statement and execute it but would prefer if there is another way allowing me to keep my typed dataset for use in e.g. ReportViewer/bindingsources)

Answer 1

You can't use a single parameter for a list of values in this way. But there may be database-specific ways to achieve what you want. For example, with SQL Server 2005 or later you could create a table-valued function to split your string parameter, something like:

CREATE FUNCTION dbo.F_Split
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

    DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
    SET @TotalLength=LEN(@InputString)
    SET @StartIndex = 1

    IF @Separator IS NULL RETURN

    WHILE @StartIndex <= @TotalLength
    BEGIN
        SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
        IF @SeparatorIndex > 0
        BEGIN
            SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex)
            SET @StartIndex = @SeparatorIndex + 1
        END
        ELSE
        BEGIN
            Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1)
            SET @StartIndex = @TotalLength+1
        END
        INSERT INTO @ValueTable
        (Value)
        VALUES
        (@Value)
    END

    RETURN
END

You would then use it as follows:

SELECT * from Table WHERE ID IN (SELECT CAST(Value AS INT) FROM F_Split(@IDs, ','))