Select where in

  • Thread starter Thread starter Sreppohcdoow
  • Start date Start date
S

Sreppohcdoow

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.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
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).
 
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
 
Back
Top