다른 사람의 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입니다.
~ 건배
답변
결국 쿼리가 작동하는 방식을 수정하지 않고 값을 변수에 저장할 수 없다는 결론에 도달했습니다. SQL 프로파일 러를 사용하여 값을 포착 한 다음 쿼리에 하드 코딩하여 작동 방식을 확인했습니다. 이 정수 배열 중 18 개가 있고 일부에는 30 개가 넘는 요소가있었습니다.
MS / SQL이 일부 추가 데이터 유형을 언어에 도입 할 필요가 있다고 생각합니다. 배열은 매우 일반적이며 왜 저장된 proc에서 사용할 수 없는지 알 수 없습니다.
답변
이런 식으로 할 수는 없지만 변수에 저장하는 전체 쿼리를 실행할 수 있습니다.
예를 들면 다음과 같습니다.
DECLARE @listOfIDs NVARCHAR(MAX) =
'1,2,3'
DECLARE @query NVARCHAR(MAX) =
'Select *
From TabA
Where TabA.ID in (' + @listOfIDs + ')'
Exec (@query)
답변
string_split문자열 목록을 사용하는 경우 SQL에 새로운 함수가 호출되었습니다 . 참조 링크 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) <> ''))
