SQL: using multiple values ​​in a single SELECT statement

Question

I am using the SELECT statement in T-SQL in a table like 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 looking for one application_id or one location_id, but what if I want to run an instruction for multiple locations? I want to return all the results for an unknown amount of location_id and application_id. For example, if I wanted to find someone in location_id 2, 3, 4, 5, but with only one application_id. How can I do it?

Thank you in advance!

---

EDIT: I'm an idiot! I did it calmly, not giving you all the details. All these values ​​are listed in the table. The user will need to select an identifier from a table column instead of inserting them. After doing some research on this issue, I came up with a page that seemed to contribute to 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 someone help me improve this to suit my needs? Sorry if my question is not very clear as I am struggling to deal with this.

---

EDIT 2:. After a more detailed reading on this subject, it seems that I need a stored procedure for this. It seems to me that the code above seems to be what I need, but I have problems setting it up in accordance with my needs. The structure of the table is as follows:

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

According to the article I just linked to:

"The correct way to handle the situation is to use a function that decompresses a row into a table. Here is a very simple function like this:"

So it seems that I need to unzip these values ​​into a string and pass them using this stored procedure. Any idea on how I can make this work?

EDIT 3: I managed to solve this with charindex () and convert () by setting them at the top. Thank you for your help, and I again apologize for being hurt.

Answer 1

Use IN as follows:

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

Answer 2

I'm not sure if I understood the < EDIT "part of your question correctly, 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 have 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 is one thing that I don’t quite understand:

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

Can you indicate what the expected result looks like?

Answer 3

You can use the 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')))