Dataset statement: ->> IN ('xx', 'yy') <<- Question

[Kbalz]

Trying to run a simple query on my dataset

the SQL statement looks like this: select * from person
where id in (@p)

When using more than one id in the "in statement", I get nothing!

Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.

However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.

How do you use the IN statement?

( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )

 

[Nicholas Paldino [.NET/C# MVP]]

Kbalz,

You can't parameterize the "in" operation, as there is no parameter that
takes a set of anything. There are a few ways you can get around this. My
personal choice when dealing with a set of integers is to have a CLR
table-based function in Sql Server (2005 and up) which will parse a
comma-delimited string and then return a table of integers (which is your
set). The list would be sent over as a comma-delimited string.

You could use T-SQL to do this as well, but it will be quite hairy, and
not as performant as using a CLR function to do the string parsing.

Then, your parameter is something like @ids (of type nvarchar(max) or
something of that nature) and you do this:

select * from person where id in (select IntValue from xfn_IntSet(@ids))

xfn_IntSet would return a single-column table which contained the parsed
integers, of course.

 

[Kbalz]

Kbalz,

You can't parameterize the "in" operation, as there is no parameter that
takes a set of anything. There are a few ways you can get around this. My
personal choice when dealing with a set of integers is to have a CLR
table-based function in Sql Server (2005 and up) which will parse a
comma-delimited string and then return a table of integers (which is your
set). The list would be sent over as a comma-delimited string.

You could use T-SQL to do this as well, but it will be quite hairy, and
not as performant as using a CLR function to do the string parsing.

Then, your parameter is something like @ids (of type nvarchar(max) or
something of that nature) and you do this:

select * from person where id in (select IntValue from xfn_IntSet(@ids))

xfn_IntSet would return a single-column table which contained the parsed
integers, of course.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Trying to run a simple query on my dataset

the SQL statement looks like this: select * from person
where id in (@p)

When using more than one id in the "in statement", I get nothing!

Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.

However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.

How do you use the IN statement?

( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )- Hide quoted text -

- Show quoted text -

Grrr! I already had the T-SQL solution, I thought a dataset would be
something I could use instead. What you say makes sense, a DS is too
strong I guess. For time's sake, I'll just uncomment my T-SQL Section
for this datasource, thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

Kbalz,

If you have SQL Server 2005, look into using a CLR table-valued
function. I've found the performance of doing the parsing using a CLR
function much, much faster than using a T-Sql function.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Kbalz,

You can't parameterize the "in" operation, as there is no parameter
that
takes a set of anything. There are a few ways you can get around this.
My
personal choice when dealing with a set of integers is to have a CLR
table-based function in Sql Server (2005 and up) which will parse a
comma-delimited string and then return a table of integers (which is your
set). The list would be sent over as a comma-delimited string.

You could use T-SQL to do this as well, but it will be quite hairy,
and
not as performant as using a CLR function to do the string parsing.

Then, your parameter is something like @ids (of type nvarchar(max) or
something of that nature) and you do this:

select * from person where id in (select IntValue from xfn_IntSet(@ids))

xfn_IntSet would return a single-column table which contained the
parsed
integers, of course.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Trying to run a simple query on my dataset

the SQL statement looks like this: select * from person
where id in (@p)

When using more than one id in the "in statement", I get nothing!

Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.

However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.

How do you use the IN statement?

( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )- Hide quoted text -

- Show quoted text -

Grrr! I already had the T-SQL solution, I thought a dataset would be
something I could use instead. What you say makes sense, a DS is too
strong I guess. For time's sake, I'll just uncomment my T-SQL Section
for this datasource, thanks.

 

[Guest]

I haven't done any testing and Nicholas may very well be right that a CLR -
hosted function would be more efficient. I use a UDF in T-SQL However, and it
seems quite fast. You can find some examples of it by searching for "fnSplit"
- it returns a Table Variable containing your delimted string value items.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder(BETA): http://www.blogmetafinder.com

Kbalz,

You can't parameterize the "in" operation, as there is no parameter that
takes a set of anything. There are a few ways you can get around this. My
personal choice when dealing with a set of integers is to have a CLR
table-based function in Sql Server (2005 and up) which will parse a
comma-delimited string and then return a table of integers (which is your
set). The list would be sent over as a comma-delimited string.

You could use T-SQL to do this as well, but it will be quite hairy, and
not as performant as using a CLR function to do the string parsing.

Then, your parameter is something like @ids (of type nvarchar(max) or
something of that nature) and you do this:

select * from person where id in (select IntValue from xfn_IntSet(@ids))

xfn_IntSet would return a single-column table which contained the parsed
integers, of course.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Trying to run a simple query on my dataset

the SQL statement looks like this: select * from person
where id in (@p)

When using more than one id in the "in statement", I get nothing!

Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.

However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.

How do you use the IN statement?

( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )- Hide quoted text -

- Show quoted text -

Grrr! I already had the T-SQL solution, I thought a dataset would be
something I could use instead. What you say makes sense, a DS is too
strong I guess. For time's sake, I'll just uncomment my T-SQL Section
for this datasource, thanks.

 

[Kbalz]

Kbalz,

If you have SQL Server 2005, look into using a CLR table-valued
function. I've found the performance of doing the parsing using a CLR
function much, much faster than using a T-Sql function.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Kbalz,
You can't parameterize the "in" operation, as there is no parameter
that
takes a set of anything. There are a few ways you can get around this.
My
personal choice when dealing with a set of integers is to have a CLR
table-based function in Sql Server (2005 and up) which will parse a
comma-delimited string and then return a table of integers (which is your
set). The list would be sent over as a comma-delimited string.
You could use T-SQL to do this as well, but it will be quite hairy,
and
not as performant as using a CLR function to do the string parsing.
Then, your parameter is something like @ids (of type nvarchar(max) or
something of that nature) and you do this:
select * from person where id in (select IntValue from xfn_IntSet(@ids))
xfn_IntSet would return a single-column table which contained the
parsed
integers, of course.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Trying to run a simple query on my dataset
the SQL statement looks like this: select * from person
where id in (@p)
When using more than one id in the "in statement", I get nothing!
Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.
However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.
How do you use the IN statement?
( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )- Hide quoted text -
- Show quoted text -

Grrr! I already had the T-SQL solution, I thought a dataset would be
something I could use instead. What you say makes sense, a DS is too
strong I guess. For time's sake, I'll just uncomment my T-SQL Section
for this datasource, thanks.- Hide quoted text -

- Show quoted text -

Well my actuall query involves about 12 tables, and a few sub-
queries. And my IN statement actually uses strings. My above query
was trying going for concept. I'll read about it still, maybe it'll
work with my mess !

 

[mark4asp]

Trying to run a simple query on my dataset

the SQL statement looks like this: select * from person
where id in (@p)

When using more than one id in the "in statement", I get nothing!

Using Visual Studio interface, there is the Execute Query button in
Query Builder. Here I can set @p = 1, and the test execute returns
the row where id = 1.. and I can do the same for @p = 2.

However when I set @p to 1,2 or '1', '2' or any combination of all
symbols, I get nothing.
When I run select * from person where id in (1, 2) in Management
Studio, I get both rows.

How do you use the IN statement?

( Its hard to search on this topic, search engines ignore the word IN
and I just get every sql statement possible )

Are you trying to use a RowFilter on your DataSet?

I managed to do a pseudo IN statement in ADO.NET (yes, a real hack) but
I can't say whether my solution would work for you - as I can't see your
data structures or database. You're right in saying that there is no IN
operator for filtering a dataset in ADO.NET - a shame really.

Microsoft - are you listening please? Can we have an IN operator for
use in row filtering in the next version of ADO.NET please, pretty
please?