How to use IN () syntax in asp.net datasets (xsd)

[arthurvanderwal]

Hi,

I use Visual studio datasets to create table adapters etc. for queries. This normally works fine for me with 'simple' queries (i.e. only 1 parameter of type string, int ...). However if I specify the following query in the dataset:

SELECT * from tblTable WHERE tblId IN (@idList)

the query editor accepts, but the generated code will create a function that just accepts a single integer. Changing the type of idList to string and passing in a string in the form of "1,2,3" also doesn't work.

Is it at all possible to use the IN keyword in queries using the datasets, and if so, what is the way to do this?

Thanks very much in advance!

Arthur

[MrMancunian]

Hi Arthur,

I'm not quite sure what you're trying to accomplish with your query, but it sounds to me like you can use a subquery. Can you explain what format your table has and what output do you expect?

Steven

[arthurvanderwal]

Hi Steven,

I like to retrieve records of which the ID is one of the IDs i supply..

so

SELECT * FROM tblTable WHERE id IN (1,2,3,4,5)

would be the thing I'm after...

However if I try this with

SELECT * FROM tblTable WHERE id IN (@idList)

I don't get it to work ...

Thanks!

Arthur

[ssnaik84]

check these methods -

DataView.RowFil ter

DataTable.Selec t

[arthurvanderwal]

Thanks ssnaik84!

However I cant help but thinking that these functions do a 'non' MSSQL operation on the dataset. Ie aren't these functions potentially slow on large result sets?

[ssnaik84]

ok.. you need MSSQL function then..

CREATE FUNCTION [dbo].[GetTableFromCSV] ( @StringInput VARCHAR(100) )
RETURNS @OutputTable TABLE ( [id] int identity(1,1), [String] NVARCHAR(4000) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END
    
    RETURN
END

to run it..

select * from dbo.GetTableFromCSV('1,2,3')

[ssnaik84]

SELECT * from tblTable WHERE tblId IN (@idList)

SELECT * from tblTable WHERE tblId IN (
select id from dbo.GetTableFromCSV('1,2,3')
)