1

Possible Duplicate:
Parameterizing an SQL IN clause?

I have read-only access to a database which I'd like to do the following on:

DECLARE @var AS INT_COLLECTION = (1,2,3)
SELECT name,column
FROM table
WHERE column IN @var

Of course INT_COLLECTION doesn't exist, but is there something similar available in SQL Server 2008? Or another way to do this without write access to the database?

Community
  • 1
  • 1
Charlie Skilbeck
  • 1,081
  • 2
  • 15
  • 38
  • 4
    This comes up so often. Sadly, `IN` just doesn't work that way :( – Yuck Jan 25 '12 at 15:19
  • Thankyou for closing my question - if it's a duplicate, can you point me to the other – Charlie Skilbeck Jan 26 '12 at 17:26
  • I didn't close it, but the duplicate question link appears at the top just under the title. Hope it helps. By the way you did receive two valid answers which you should try out. – Yuck Jan 26 '12 at 17:31

2 Answers2

4

This is a pretty heavy-handed approach since you have to create a user-defined table type, but it does work:

CREATE TYPE INT_COLLECTION AS TABLE (
  Value Int
);
GO

DECLARE @var AS INT_COLLECTION;
INSERT INTO @var (Value)
VALUES (1), (2), (3);

SELECT name, col
FROM YourTable
WHERE col IN (SELECT Value FROM @var)
Yuck
  • 49,664
  • 13
  • 105
  • 135
  • I think, it is no where mentioned from user defined table type as OP is using User defined table as mentioned in the query. Right ? – Pankaj Jan 25 '12 at 16:12
  • @StackOverflowUser I'm sorry, I don't understand the comment. – Yuck Jan 25 '12 at 16:15
  • I guess the question was specified a little poorly - I need to pass in an argument (variable) which is a variable sized list of integers (not strings, although if they became strings at some point that would be OK I guess, performance won't be an issue here). Creating the table just moves the problem up a level - how do I pass in the list of ints with which to create the table? Also, can I create a table if I have only read access? – Charlie Skilbeck Jan 27 '12 at 16:22
1

You could do something like this:

DECLARE @var AS TABLE (IDS INT);
INSERT INTO @var  VALUES (1),(2),(3);

SELECT name,column
FROM table
WHERE column IN ( SELECT IDS FROM @var)
Mithrandir
  • 24,869
  • 6
  • 50
  • 66