정수 목록을 보유할 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 주입에 취약합니다.필요에 따라 청소합니다.

SQL Server 2016+ 및 Azure SQL Database의 경우 이 문제에 대한 완벽한 솔루션이 되는 STRING_SPLIT 함수가 추가되었습니다.다음은 매뉴얼입니다.https://learn.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 입니다.

결국 쿼리의 작동 방식을 수정하지 않으면 값을 변수에 저장할 수 없다는 결론에 도달했습니다.SQL 프로파일러를 사용하여 값을 캐치한 후 쿼리에 하드코딩하여 동작 상태를 확인했습니다.이러한 정수 배열은 18개이며, 30개 이상의 요소가 포함된 배열도 있습니다.

MS/SQL이 몇 가지 부가 데이터형을 언어에 도입할 필요가 있다고 생각합니다.어레이는 매우 일반적입니다.스토어드 프로시저에서 어레이를 사용할 수 없었던 이유를 알 수 없습니다.

에는 SQL이라는 새로운 .string_split문자열 목록을 사용하는 경우.참조 링크 STRING_SPLIT(Transact-SQL)

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

은 '먹다'로 할 수 있습니다.in음음음같 뭇매하다

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

나는 이것을 사용한다:

1-구축 중인 스크립트에서 임시 테이블 변수를 선언합니다.

DECLARE @ShiftPeriodList TABLE(id INT NOT NULL);

2-온도 테이블에 할당:

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-WHERE 스테이트먼트에서 필요할 때 표를 참조합니다.

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

이렇게는 할 수 없지만 변수에 저장된 전체 쿼리를 실행할 수 있습니다.

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

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

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

Exec (@query)

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