SQL: Use multiple values in single SELECT statement


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
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
      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

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.

Best Solution

Use IN as follows:

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