SQL: Use multiple values in single SELECT statement

Question

I'm using a SELECT statement in T-SQL on a table similar to this:

SELECT DISTINCT name, location_id, application_id FROM apps
WHERE ((application_id is null) or (application_id = '4'))
AND ((location_id is null) or (location_id = '3'))

This seems to work fine when searching for one application_id or one location_id, but what if I want to run the statement for multiple locations? I want to return all results for an unknown amount of location_id's and application_id's. For example, if I wanted to search for someone at the location_id's 2, 3, 4, 5 but with only one application_id. How would I do this?

Thank you in advance!

---

EDIT: I'm an idiot! I made it sound easy without giving you all the full details. All of these values are given from inside a table. The user will have to choose the id's from a column in the table instead of inserting them. After doing a bit of research on this problem I came up with a page that seemed to tout a viable solution.

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END

Can anyone help me perfect this to meet my needs? Sorry if my question isn't very clear, as I'm struggling to get to grips with it myself.

---

EDIT 2: After doing a bit more reading on the subject it seems that I need a stored procedure to do this. The code up the top seems to be what I need, but I'm having trouble tailoring it to my needs. The table structure is as follows:

application_id   name                  location_id
------------------------------------------------------
1                Joe Blogs             34
2                John Smith            55

According to the article I've just linked to:

"The correct way of handling the situation is to use a function that unpacks the string into a table. Here is a very simple such function:"

So, it seems that I need to unpack these values into a string and pass them through using this stored procedure. Any idea on how I can get this to work?

EDIT 3: I've managed to solve it using charindex() and convert(), whilst setting them at the top. Thank you for all your help and I apologise again for being a pain.

Answer 1

Use IN as follows:

location_id IN ('2', '3', '4', '5')

Answer 2

You can use an IN clause as follows :-

SELECT DISTINCT name, location_id, application_id FROM apps
WHERE ((application_id is null) or (application_id = '4'))
AND ((location_id is null) or (location_id in ('2', '3', '4', '5')))

Answer 3

If I understand the question could you not just take out the OR part for the application_id and the location_id?

SELECT DISTINCT name, location_id, application_id 
FROM apps
WHERE application_id is NULL AND location_id is NULL

or you can use the IN clause for a particular list of locations, based on 2, 3, 4, 5.

SELECT DISTINCT name, location_id, application_id 
FROM apps
WHERE application_id is NULL AND location_id IN ('2','3','4','5')

Answer 4

As everyone else suggested use the IN syntax

Transact SQL Reference for IN - http://msdn.microsoft.com/en-us/library/ms177682(SQL.90).aspx

Answer 5

I'm not sure if I understood the "EDIT" part of your question right, but do you mean something like this?

SELECT DISTINCT name, location_id, application_id 
FROM apps
WHERE location_id IN 
(
    SELECT id FROM other_table
)

EDIT:
Now that I read your second edit, I'm still not sure if I understand what you REALLY want to do.

Of course, we can help you with your stored procedure, but there's one thing which is not quite clear to me:

Do you want us to help you create the SP?
Or do you really want to do something else, but think you must do it by creating a SP because the link said so?

Can you specify how the expected result looks like?