Dynamic sets? (i.e. where x in (@set))

[Not Me]

Hi,

I've tried without success to create a function where one of the
parameters determines the final line of a select statement.

The final line includes a 'where referencenumber in (@set)' line.. and
@set is the parameter I wish to use which is a string.

If the set is just one value, it works fine, however with anything more
than that I just get no records returned.. If I output the parameter,
and copy and paste that into query analyser I do get the correct result,
so it's nothing to do with string delimiters etc..

Any ideas to fix this problem? Or am I going to have to dynamic sql the
whole thing?

Cheers,
Chris

[Vaxman]

Take a look at this for a good overview of techniques
http://www.sommarskog.se/arrays-in-sql.html

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[--CELKO--]

>> The final line includes a 'where referencenumber in (@set)' line..
and @set is the parameter I wish to use which is a string. <<

There is nothign wrong with a string dataytpe parameter. Apparently,
you don't know that parameters are SCALAR, not data structures. There
is one and only data structure in SQL, the table. Furthermore, SQL is
a COMPILED LANGUAGE, not an interpreter.

If the set is just one value, it works fine, however with anything more than that I just get no records [sic] returned.. <<

Rows are not anything like records. Chris, you have missed the
foundations of the RDBMS model. I can give a dangerous kludge in the
form of single SQL statement that runs like glue while it parses a CSV
string and CAST() the piece into the target datatype (which you did
not tell us, along with the DDL that you did not post).
Any ideas to fix this problem? Or am I going to have to dynamic sql

the whole thing? <<

The right way to do this is the load a table with values, add
constaints to this table and make sure that you don't invite dirty
data:

In the schema, build your set and clean it up

CREATE TABLE PickList
(pick INTEGER NOT NULL PRIMARY KEY
CHECK (pick BETWEEN 1 AND 100));

In the front end, load the set:
DELETE FROM PickList;
INSERT INTO Picklist VALUES (..);

When SQL Server finally gets to the 1992 Standards a dozen years late,
this can be done in one INSERT statement, but for now you have to
write procedural code.

SELECT..
FROM ..
WHERE x IN (SELECT pick FROM PickList);

More work? Yes! Safer, portable, and fast? Yes, much!