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.

Posting the table definition and some example rows, queries and expected answers would improve the quality of the answers. — Andomar
– Andomar, Commented Jun 23, 2009 at 9:49
Apologies for not giving a full explanation, just experiencing a couple of technical difficulties while I'm racking my brains with this problem. — Mike B
– Mike B, Commented Jun 23, 2009 at 10:06
It's still not clear what the problem is. Is the problem you just can't do what you want to do (why can't you just build the IN statement in code from the user input?) - or are you concerned about the security issues raised by the linked article? I would only really worry about the latter if this is an app sitting on the internet. — Joel Goodwin
– Joel Goodwin, Commented Jun 23, 2009 at 10:12
The problem is as it says in the article. I get this kind of error when using IN "Syntax error converting the varchar value '9, 12, 27, 37' to a column of data type int." These location_id's need to be checked amongst those that exist within the column in the database and as this is for a web application. After looking at this procedure the main point for me seems to be charindex(). I'd like to be able to make a table from this "list" of pre-inserted values so that they can be checked. — Mike B
– Mike B, Commented Jun 23, 2009 at 10:35
Sounds like you've built the IN like this: IN ('9, 12, 27, 37') and your underlying column is an int (as it appeared). Try using: IN (9, 12, 27, 37). No quotes. — Joel Goodwin
– Joel Goodwin, Commented Jun 23, 2009 at 10:43

Joel Goodwin · Accepted Answer · 2009-06-23 09:28:02Z

17

Use IN as follows:

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

answered Jun 23, 2009 at 9:28

Joel Goodwin

5,09629 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christian Specht · Accepted Answer · 2009-06-23 11:11:35Z

3

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?

edited Jun 23, 2009 at 11:11

answered Jun 23, 2009 at 9:49

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

1 Comment

Mike B Over a year ago

Yes, I'd like help with the SP. For this script I would like it so that it would search all the location_id's if none are suggested, meaning that it would need to take those within the column and put those through. For example, if I were looking for a man named Joe King and no location_id were specified the select statement would search using all of them.

Steve Weet · Accepted Answer · 2009-06-23 09:29:56Z

0

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')))

answered Jun 23, 2009 at 9:29

Steve Weet

28.5k11 gold badges72 silver badges86 bronze badges

Collectives™ on Stack Overflow

SQL: Use multiple values in single SELECT statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related