Select .... where the Item.ID is in this array?

[Robin Tucker]

Hiya,
I want to populate a list view (in a variety of ways), but I would rather
not execute my function once for every record I want to show in the list.
What I want to do is pass an array of "keys" into a stored procedure and
have that procedure return one recordset with all of the records in. So
what I want to do is something like:

CREATE PROCEDURE MySuperProcedure

@MyArrayOfIDs (some way of specifying an array of integers),

AS
SELECT *
FROM MyTable
WHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )
Obviously this syntax is not available. Is there another way of doing this?

[Terry Kreft]

Something like

Create Procedure MySuperProcedure
@MyArrayOfIDs varchar(8000)
AS
SELECT *
FROM MyTable
WHERE CHARINDEX(convert(varchar, MyTable.MyField) + '', convert(char,
@MyArrayOfIDs)) <> 0
Sample call
EXEC MySuperProcedure '1, 6'

Returns
MyField
-----------
1
6

(2 row(s) affected)

--
Terry Kreft
MVP Microsoft Access
"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:cg*******************@news.demon.co.uk...

Hiya,
I want to populate a list view (in a variety of ways), but I would rather
not execute my function once for every record I want to show in the list.
What I want to do is pass an array of "keys" into a stored procedure and
have that procedure return one recordset with all of the records in. So
what I want to do is something like:

CREATE PROCEDURE MySuperProcedure

@MyArrayOfIDs (some way of specifying an array of integers),

AS
SELECT *
FROM MyTable
WHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )
Obviously this syntax is not available. Is there another way of doing this?

[Robin Tucker]

Nice one. Thanks very much Terry.

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:vT********************@karoo.co.uk...

Something like

Create Procedure MySuperProcedure
@MyArrayOfIDs varchar(8000)
AS
SELECT *
FROM MyTable
WHERE CHARINDEX(convert(varchar, MyTable.MyField) + '', convert(char,
@MyArrayOfIDs)) <> 0
Sample call
EXEC MySuperProcedure '1, 6'

Returns
MyField
-----------
1
6

(2 row(s) affected)

--
Terry Kreft
MVP Microsoft Access
"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:cg*******************@news.demon.co.uk...

Hiya,
I want to populate a list view (in a variety of ways), but I would rather
not execute my function once for every record I want to show in the list.
What I want to do is pass an array of "keys" into a stored procedure and
have that procedure return one recordset with all of the records in. So
what I want to do is something like:

CREATE PROCEDURE MySuperProcedure

@MyArrayOfIDs (some way of specifying an array of integers),

AS
SELECT *
FROM MyTable
WHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )
Obviously this syntax is not available. Is there another way of doing

this?

[Erland Sommarskog]

Robin Tucker (id*************************@reallyidont.com) writes:

I want to populate a list view (in a variety of ways), but I would rather
not execute my function once for every record I want to show in the list.
What I want to do is pass an array of "keys" into a stored procedure and
have that procedure return one recordset with all of the records in. So
what I want to do is something like:

CREATE PROCEDURE MySuperProcedure

@MyArrayOfIDs (some way of specifying an array of integers),

AS
SELECT *
FROM MyTable
WHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )

Obviously this syntax is not available. Is there another way of doing
this?

While the syntax Terry showed you is likely to work, the performance is
utterly poor when the list grow in size, not the least since it cannot
use any index on the column in question.

Look at http://www.sommarskog.se/arrays-in-s...st-of-integers
for a more efficent solution. (The article presents even more effecient
solutions than this one, but this is the is easiest to use.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp