정수 목록을 보유 할 SQL 변수

정수 목록을 보유 할 SQL 변수

---

다른 사람의 SQL 보고서를 디버깅하려고하고 기본 보고서 쿼리를 SQL 2012의 쿼리 창에 배치했습니다.

보고서가 요구하는 매개 변수 중 하나는 정수 목록입니다. 이는 다중 선택 드롭 다운 상자를 통해 보고서에서 수행됩니다. 보고서의 기본 쿼리는 다음과 같은 where절 에서이 정수 목록을 사용합니다.

select *
from TabA
where TabA.ID in (@listOfIDs)

디버깅하는 쿼리를 수정하고 싶지 않지만 SQL Server에서 테스트를 위해 이러한 유형의 데이터를 보유 할 수있는 변수를 만드는 방법을 알 수 없습니다.

예 :

declare @listOfIDs int
set listOfIDs  = 1,2,3,4

정수 목록을 보유 할 수있는 데이터 유형이 없으므로 보고서와 동일한 값으로 SQL Server에서 보고서 쿼리를 어떻게 실행할 수 있습니까?

---

테이블 변수

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

select *
from TabA
where TabA.ID in (select id from @listOfIDs)

또는

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end

select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

---

변수를 가정하면 다음과 유사합니다.

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

저장 프로시 저는 다음 형식으로 사용합니다.

ALTER Procedure [dbo].[GetFooByIds]
    @Ids [IntList] ReadOnly
As 

IntList를 작성하고 다음과 같이 프로 시저를 호출 할 수 있습니다.

Declare @IDs IntList;
Insert Into @IDs Select Id From dbo.{TableThatHasIds}
Where Id In (111, 222, 333, 444)
Exec [dbo].[GetFooByIds] @IDs

또는 IntList를 직접 제공하는 경우

DECLARE @listOfIDs dbo.IntList
INSERT INTO @listofIDs VALUES (1),(35),(118);

---

SQL Server에 정수 목록을 보유 할 수있는 데이터 유형이 없습니다. 그러나 당신이 할 수있는 일은 정수 목록을 문자열로 저장하는 것입니다.

DECLARE @listOfIDs varchar(8000);
SET @listOfIDs = '1,2,3,4';

그런 다음 문자열을 별도의 정수 값으로 분할하여 테이블에 넣을 수 있습니다. 당신의 절차는 이미 이것을 할 수 있습니다.

동적 쿼리를 사용하여 동일한 결과를 얻을 수도 있습니다.

DECLARE @SQL nvarchar(8000);

SET @SQL = 'SELECT * FROM TabA WHERE TabA.ID IN (' + @listOfIDs + ')';
EXECUTE (@SQL);

---

SQL Server 2016+ 및 Azure SQL Database의 경우 STRING_SPLIT 함수가 추가되어이 문제에 대한 완벽한 솔루션이되었습니다. 다음은 설명서입니다. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

예를 들면 다음과 같습니다.

/*List of ids in a comma delimited string
  Note: the ') WAITFOR DELAY ''00:00:02''' is a way to verify that your script 
        doesn't allow for SQL injection*/
DECLARE @listOfIds VARCHAR(MAX) = '1,3,a,10.1,) WAITFOR DELAY ''00:00:02''';

--Make sure the temp table was dropped before trying to create it
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;

--Create example reference table
CREATE TABLE #MyTable
([Id] INT NOT NULL);

--Populate the reference table
DECLARE @i INT = 1;
WHILE(@i <= 10)
BEGIN
    INSERT INTO #MyTable
    SELECT @i;

    SET @i = @i + 1;
END

/*Find all the values
  Note: I silently ignore the values that are not integers*/
SELECT t.[Id]
FROM #MyTable as t
    INNER JOIN 
        (SELECT value as [Id] 
        FROM STRING_SPLIT(@listOfIds, ',')
        WHERE ISNUMERIC(value) = 1 /*Make sure it is numeric*/
            AND ROUND(value,0) = value /*Make sure it is an integer*/) as ids
    ON t.[Id] = ids.[Id];

--Clean-up
DROP TABLE #MyTable;

쿼리 결과는 1,3입니다.

~ 건배

---

In the end i came to the conclusion that without modifying how the query works i could not store the values in variables. I used SQL profiler to catch the values and then hard coded them into the query to see how it worked. There were 18 of these integer arrays and some had over 30 elements in them.

I think that there is a need for MS/SQL to introduce some aditional datatypes into the language. Arrays are quite common and i don't see why you couldn't use them in a stored proc.

---

You can't do it like this, but you can execute the entire query storing it in a variable.

For example:

DECLARE @listOfIDs NVARCHAR(MAX) = 
    '1,2,3'

DECLARE @query NVARCHAR(MAX) = 
    'Select *
     From TabA
     Where TabA.ID in (' + @listOfIDs + ')'

Exec (@query)

---

There is a new function in SQL called string_split if you are using list of string. Ref Link STRING_SPLIT (Transact-SQL)

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

you can pass this query with in as follows:

SELECT *
  FROM [dbo].[yourTable]
  WHERE (strval IN (SELECT value FROM STRING_SPLIT(@tags, ',') WHERE RTRIM(value) <> ''))

---

I use this :

1-Declare a temp table variable in the script your building:

DECLARE @ShiftPeriodList TABLE(id INT NOT NULL);

2-Allocate to temp table:

IF (SOME CONDITION) 
BEGIN 
        INSERT INTO @ShiftPeriodList SELECT ShiftId FROM [hr].[tbl_WorkShift]
END
IF (SOME CONDITION2)
BEGIN
    INSERT INTO @ShiftPeriodList
        SELECT  ws.ShiftId
        FROM [hr].[tbl_WorkShift] ws
        WHERE ws.WorkShift = 'Weekend(VSD)' OR ws.WorkShift = 'Weekend(SDL)'

END

3-Reference the table when you need it in a WHERE statement :

INSERT INTO SomeTable WHERE ShiftPeriod IN (SELECT * FROM @ShiftPeriodList)

참고URL : https://stackoverflow.com/questions/18371968/sql-variable-to-hold-list-of-integers