IN 연산자와 함께 사용할 변수 정의 (T-SQL)

IN 연산자와 함께 사용할 변수 정의 (T-SQL)

---

IN 연산자를 사용하는 Transact-SQL 쿼리가 있습니다. 이 같은:

select * from myTable where myColumn in (1,2,3,4)

전체 목록 "(1,2,3,4)"를 보유 할 변수를 정의하는 방법이 있습니까? 어떻게 정의해야합니까?

declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList

---

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

---

DECLARE @mylist TABLE (Id int)
INSERT INTO @mylist
SELECT id FROM (VALUES (1),(2),(3),(4),(5)) AS tbl(id)

SELECT * FROM Mytable WHERE theColumn IN (select id from @mylist)

---

다음과 같은 기능을 사용하십시오.

CREATE function [dbo].[list_to_table] (@list varchar(4000))
returns @tab table (item varchar(100))
begin

if CHARINDEX(',',@list) = 0 or CHARINDEX(',',@list) is null
begin
    insert into @tab (item) values (@list);
    return;
end


declare @c_pos int;
declare @n_pos int;
declare @l_pos int;

set @c_pos = 0;
set @n_pos = CHARINDEX(',',@list,@c_pos);

while @n_pos > 0
begin
    insert into @tab (item) values (SUBSTRING(@list,@c_pos+1,@n_pos - @c_pos-1));
    set @c_pos = @n_pos;
    set @l_pos = @n_pos;
    set @n_pos = CHARINDEX(',',@list,@c_pos+1);
end;

insert into @tab (item) values (SUBSTRING(@list,@l_pos+1,4000));

return;
end;

like를 사용하는 대신 함수가 반환 한 테이블로 내부 조인을 만듭니다.

select * from table_1 where id in ('a','b','c')

된다

select * from table_1 a inner join [dbo].[list_to_table] ('a,b,c') b on (a.id = b.item)

인덱싱되지 않은 1M 레코드 테이블에서 두 번째 버전은 약 절반의 시간이 걸렸습니다 ...

건배

---

TSQL 쿼리에 대한 동적 csv 목록을 처리하는 방법에는 두 가지가 있습니다.

1) 내부 선택 사용

SELECT * FROM myTable WHERE myColumn in (SELECT id FROM myIdTable WHERE id > 10)

2) 동적으로 연결된 TSQL 사용

DECLARE @sql varchar(max)  
declare @list varchar(256)  
select @list = '1,2,3'  
SELECT @sql = 'SELECT * FROM myTable WHERE myColumn in (' + @list + ')'

exec sp_executeSQL @sql

3) 가능한 세 번째 옵션은 테이블 변수입니다. SQl Server 2005가있는 경우 테이블 변수를 사용할 수 있습니다. Sql Server 2008에서 전체 테이블 변수를 저장 프로 시저에 매개 변수로 전달하고이를 조인 또는 IN 절의 하위 선택으로 사용할 수도 있습니다.

DECLARE @list TABLE (Id INT)

INSERT INTO @list(Id)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4


SELECT
    * 
FROM 
    myTable
    JOIN @list l ON myTable.myColumn = l.Id

SELECT
    * 
FROM 
    myTable
WHERE
    myColumn IN (SELECT Id FROM @list)

---

DECLARE @myList TABLE (Id BIGINT) INSERT INTO @myList(Id) VALUES (1),(2),(3),(4);
select * from myTable where myColumn in(select Id from @myList)

Please note that for long list or production systems it's not recommended to use this way as it may be much more slower than simple INoperator like someColumnName in (1,2,3,4) (tested using 8000+ items list)

---

No, there is no such type. But there are some choices:

• Dynamically generated queries (sp_executesql)
• Temporary tables
• Table-type variables (closest thing that there is to a list)
• Create an XML string and then convert it to a table with the XML functions (really awkward and roundabout, unless you have an XML to start with)

None of these are really elegant, but that's the best there is.

---

Starting with SQL2017 you can use STRING_SPLIT and do this:

declare @myList nvarchar(MAX)
set @myList = '1,2,3,4'
select * from myTable where myColumn in (select value from STRING_SPLIT(@myList,','))

---

If you want to do this without using a second table, you can do a LIKE comparison with a CAST:

DECLARE @myList varchar(15)
SET @myList = ',1,2,3,4,'

SELECT *
FROM myTable
WHERE @myList LIKE '%,' + CAST(myColumn AS varchar(15)) + ',%'

If the field you're comparing is already a string then you won't need to CAST.

Surrounding both the column match and each unique value in commas will ensure an exact match. Otherwise, a value of 1 would be found in a list containing ',4,2,15,'

---

slight improvement on @LukeH, there is no need to repeat the "INSERT INTO": and @realPT's answer - no need to have the SELECT:

DECLARE @MyList TABLE (Value INT) 
INSERT INTO @MyList VALUES (1),(2),(3),(4)

SELECT * FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

---

I know this is old now but TSQL => 2016, you can use STRING_SPLIT:

DECLARE @InList varchar(255) = 'This;Is;My;List';

WITH InList (Item) AS (
    SELECT value FROM STRING_SPLIT(@InList, ';')
)

SELECT * 
FROM [Table]
WHERE [Item] IN (SELECT Tag FROM InList)

---

As no one mentioned it before, starting from Sql Server 2016 you can also use json arrays and OPENJSON (Transact-SQL):

declare @filter nvarchar(max) = '[1,2]'

select *
from dbo.Test as t
where
    exists (select * from openjson(@filter) as tt where tt.[value] = t.id)

You can test it in sql fiddle demo

You can also cover more complicated cases with json easier - see Search list of values and range in SQL using WHERE IN clause with SQL variable?

---

DECLARE @StatusList varchar(MAX);
SET @StatusList='1,2,3,4';
DECLARE @Status SYS_INTEGERS;
INSERT INTO  @Status 
SELECT Value 
FROM dbo.SYS_SPLITTOINTEGERS_FN(@StatusList, ',');
SELECT Value From @Status;

---

I think you'll have to declare a string and then execute that SQL string.

Have a look at sp_executeSQL

참고URL : https://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql