-3

I have that sql code

SELECT * FROM table1
WHERE id IN (1,2,3,4,5,6)

I would like to have that expression

DECLARE @allNeededIds ??? = (1,2,3,4,5,6)

SELECT * FROM table1
WHERE id IN @allNeededIds

Could I declare something like this in SQL or should I use table, how will code looks then? I use MS Sql Server

Jacek
  • 11,661
  • 23
  • 69
  • 123
  • Does `id` is numeric field? – Maciej Los Jun 24 '15 at 12:49
  • I *was* going to flag this as a duplicate of http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function, but it appears to be hideously out-of-date. Still, I expect there is a more-up-to-date duplicate out there. – LittleBobbyTables - Au Revoir Jun 24 '15 at 12:50
  • @MaciejLos: For example it could be INT, I just need know how code should look – Jacek Jun 24 '15 at 12:52
  • @LittleBobbyTables here's a pretty up-to date answer http://stackoverflow.com/a/43767/119477 – Conrad Frix Jun 24 '15 at 12:56
  • @ConradFrix - yeah, but that's just a bunch of links, yuck – LittleBobbyTables - Au Revoir Jun 24 '15 at 12:56
  • possible duplicate of [T-SQL stored procedure that accepts multiple Id values](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) – A ツ Jun 24 '15 at 12:57
  • SQL Server doesn't support arrays or lists, etc. You either have to fudge it *(Dynamic SQL, storing values in string lists then using table-valued-functions to split the string up, etc)* or use tables directly *(including table variables, temporary tables, permanent tables, etc)* – MatBailie Jun 24 '15 at 13:14
  • 1
    @MatBailie - `xml` ought to be somewhere in your list also. At least it's *designed* for holding multiple values, unlike strings, and there are built in functions that take in XML and give you back *sets*, unlike strings. – Damien_The_Unbeliever Jun 24 '15 at 13:27
  • @Damien_The_Unbeliever - Not used SQL Server in a long time, forgot about XML support :) Much better that `split_list()` udf's. – MatBailie Jun 24 '15 at 13:28

4 Answers4

9

Use a table-variable:

DECLARE @allNeededIds table(id int not null)
insert into @allNeededIds (id) values (1),(2),(3),(4),(5),(6)

SELECT * FROM table1
WHERE id IN (select id from @allNeededIds)

If you wanted to pass this set of values into this code from an outside source (e.g. calling this code in a stored procedure or running it from another language), you should look at Table-Valued parameters

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
3

You could use Dynamic SQL to do this.

DECLARE @MyTSQLStatement nvarchar(max)

DECLARE @allNeededIds nvarchar(max) 
SET @allNeededIds =  '(1,2,3,4,5,6)'

SET @MyTSQLStatement = 'SELECT * FROM table1 WHERE id IN ' + @allNeededIds

EXEC sp_executesql @MyTSQLStatement
LDMJoe
  • 1,591
  • 13
  • 17
  • I don't think it's the case for Dynamic SQL. – Radu Gheorghiu Jun 24 '15 at 12:51
  • How else does it allow for one variable to constitute the entirety of the IN clause, as specified by the OP? – LDMJoe Jun 24 '15 at 12:54
  • I didn't downvote your answer, but I think implementing the logic that will generate the 1 through 6 values in a `SELECT`, similar to my answer would be better than generating and executing a dynamic SQL, even if the outcome will be the same. – Radu Gheorghiu Jun 24 '15 at 12:55
  • 1
    @RaduGheorghiu I think it's a better technique too, but the OP explicitly wants to assign a comma separated list to one variable (I assume for an input parameter to a procedure). If you want to turn that into a sub-select as in your answer, we probably should show him how to parse out the string to extract the values for inserting into the temp table too. – LDMJoe Jun 24 '15 at 13:00
  • 1
    The OP seemed to be quite open about what approach they were taking so I'd rather point them towards e.g. table-valued parameters (something *designed* for multiple values) rather than suggesting a string parameter and then having to pull it apart. – Damien_The_Unbeliever Jun 24 '15 at 13:08
  • @Damien_The_Unbeliever I can be on board with that. I guess I took his pseudocode a little too literal as a requirement. – LDMJoe Jun 24 '15 at 13:12
2

What you can do is something like this:

SELECT *
FROM table1
WHERE id IN (SELECT id FROM table2)

in which scenario SELECT id FROM table2 would return the values 1 through 6.

Of course, if the inner SELECT will return more values than the ones you need, you will need to adjust the query by using an additional WHERE clause to filter just the values you need.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • If you downvoted my answer please post a comment, I'm really curious what is wrong with my solution and what could be improved. – Radu Gheorghiu Jun 24 '15 at 12:56
1

First create a Split function which will take a comma separated string as input and return a table:

CREATE FUNCTION [dbo].Split(@input AS Varchar(4000) )
RETURNS
      @Result TABLE(Value BIGINT)
AS
BEGIN
      DECLARE @str VARCHAR(20)
      DECLARE @ind Int
      IF(@input is not null)
      BEGIN
            SET @ind = CharIndex(',',@input)
            WHILE @ind > 0
            BEGIN
                  SET @str = SUBSTRING(@input,1,@ind-1)
                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
                  INSERT INTO @Result values (@str)
                  SET @ind = CharIndex(',',@input)
            END
            SET @str = @input
            INSERT INTO @Result values (@str)
      END
      RETURN
END

Second, do this to get result:

DECLARE @allNeededIds as varchar(max)
set @allNeededIds = '1,2,3,4,5,6'
select value into temp from Split(@allNeededIds)

Final query would be:

SELECT * FROM table1
WHERE id IN (select value in temp)
m.cekiera
  • 5,365
  • 5
  • 21
  • 35
VictoryKing
  • 11
  • 2