Is there a way to select to find multiple conditions?

I.e., in SQL, you can typically SELECT from myTable where someColumn in
('x','y','z')
instead of passing in explicit strings, I'd like to pass in an array of
values, is this possible, i.e., perhaps just a single column as a dataview?

Can a nested select be used as can be used in SQL?

I haven't seen may in-depth Datatable.Select examples that demonstrate such
functionality... if they are out there, please point the way!

Thx,
MS

Sure. First consider that the IN clause can't accept a parameter so you'll
have to deal with this in another way. One approach that I discuss in my new
book is to create a Table-Value Function that converts a delimited list into
a SQL Table-datatype variable. This is passed to the IN clause with a SELECT
as in:

.... WHERE myValue IN (SELECT valCol FROM myTVF(@DelimitedListParm))

This TVF can be implemented in TSQL or by using a CLR function--both are
illustrated in the book... and elsewhere on the web.
Show quoteHide quote

Hi,

Yes, it sucks, I know. There are several ways but perhaps the most easy one
is to use dynamically built sql command if your list consists of numbers
(there is no danger of sql injection in this case as you can check whether
only numbers are being passed).

Show quoteHide quote

My preferred method for this is to build an xml string within the
calling application and then pass that into the stored procedure

from there you can swiftly parse the xml using built in sql server
functions and then use the results in an IN statement.

See this knowledge base article for further information: -
http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

Good solution, but you need a stored procedure...

Show quoteHide quote

Try this...


-----
SET NOCOUNT ON

CREATE TABLE Employees
(
EM_ID int PRIMARY KEY IDENTITY,
EM_Class char(1) NOT NULL
);

INSERT Employees VALUES ('A');
INSERT Employees VALUES ('B');
INSERT Employees VALUES ('C');
INSERT Employees VALUES ('D');
go

CREATE PROCEDURE pr_Employees_GetByClass
@EM_Class varchar(100)
AS

DECLARE @T1 TABLE (F1 varchar(100))
INSERT @T1
SELECT @EM_Class


WHILE (CHARINDEX(',',@EM_Class)>0)
BEGIN
  INSERT INTO @T1 (F1)
  SELECT VALUE = LTRIM(RTRIM(SUBSTRING(@EM_Class,1,CHARINDEX(',',@EM_Class)-1)))
  SET @EM_Class =
SUBSTRING(@EM_Class,CHARINDEX(',',@EM_Class)+LEN(','),LEN(@EM_Class))
END


SELECT *
FROM Employees
WHERE EM_Class IN (SELECT F1 FROM @T1)
go

EXEC pr_Employees_GetByClass 'A,C,D,'

-- Results --

EM_ID       EM_Class
----------- --------
1           A
3           C
4           D



Show quoteHide quote

TableAdapters and true N-Tier
Error accessing MS Access database with Linked Tables on fileshare
Create my own db connection wizard
A problem with the datagrid
FileSystemWatcher does not seem to be working