[sql-server] 여러 ID 값을 허용하는 T-SQL 저장 프로 시저

Program / 글쓴이

매개 변수로 ID 목록을 저장 프로 시저에 전달하는 것을 처리하는 우아한 방법이 있습니까?

예를 들어, 저장 프로 시저에서 부서 1, 2, 5, 7, 20을 반환하려고합니다. 과거에는 아래 코드와 같이 쉼표로 구분 된 ID 목록을 전달했지만 실제로는 더럽습니다.

SQL Server 2005는 내가 적용 할 수있는 유일한 제한 사항입니다.

create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)

답변

Erland Sommarskog는 지난 16 년 동안이 질문에 대한 권위있는 답변을 유지했습니다 . SQL Server의 배열 및 목록 .

배열이나 목록을 쿼리에 전달하는 방법에는 적어도 12 가지가 있습니다. 각각 고유 한 장단점이 있습니다.

  • 테이블 반환 매개 변수 . SQL Server 2008 이상에서만 사용 가능하며 범용 “최상의”접근 방식에 가장 가깝습니다.
  • 반복적 방법 . 구분 된 문자열을 전달하고 반복하십시오.
  • CLR 사용 . .NET 언어의 SQL Server 2005 이상.
  • XML . 많은 행을 삽입하는 데 매우 좋습니다. SELECT에 과잉 일 수 있습니다.
  • 숫자 표 . 간단한 반복 방법보다 성능 / 복잡성이 높습니다.
  • 고정 길이 요소 . 고정 길이는 구분 문자열보다 속도를 향상시킵니다.
  • 숫자의 기능 . 테이블에서 가져 오는 것이 아니라 함수에서 생성되는 숫자 및 고정 길이의 변형.
  • 재귀 공통 테이블 식 (CTE). SQL Server 2005 이상에서는 반복 방법보다 여전히 복잡하지 않고 성능이 떨어집니다.
  • 동적 SQL . 속도가 느리고 보안에 영향을 줄 수 있습니다.
  • 많은 매개 변수를 목록으로 전달 . 지루하고 오류가 발생하기 쉽지만 간단합니다.
  • 정말 느린 방법 . charindex, patindex 또는 LIKE를 사용하는 메소드

나는이 모든 옵션들 사이의 타협점을 배우기 위해 기사 를 읽을 만큼 충분히 권장 할 수는 없다 .

답변

예, 현재 솔루션은 SQL 주입 공격에 취약합니다.

내가 찾은 가장 좋은 해결책은 텍스트를 단어로 나누는 기능을 사용하는 것입니다 (여기에 게시되어 있거나 블로그 에서이 기능 을 사용할 수 있습니다 ). 다음과 같은 것 :

SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId

답변

값을 많이 사용하려는 경우 고려해야 할 방법 중 하나는 먼저 임시 테이블에 값을 쓰는 것입니다. 그런 다음 평상시처럼 참여하십시오.

이렇게하면 한 번만 구문 분석합니다.

‘Split’UDF 중 하나를 사용하는 것이 가장 쉽지만 많은 사람들이 그 예를 게시했습니다. 나는 다른 경로로 갈 것이라고 생각했습니다.)

이 예에서는 (#tmpDept)에 조인 할 임시 테이블을 만들고 전달한 부서 ID로 채 웁니다. 쉼표로 구분한다고 가정하지만 물론 변경할 수 있습니다. 당신이 원하는대로.

IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL
BEGIN
    DROP TABLE #tmpDept
END

SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','')

CREATE TABLE #tmpDept (DeptID INT)
DECLARE @DeptID INT
IF IsNumeric(@DepartmentIDs)=1
BEGIN
    SET @DeptID=@DepartmentIDs
    INSERT INTO #tmpDept (DeptID) SELECT @DeptID
END
ELSE
BEGIN
        WHILE CHARINDEX(',',@DepartmentIDs)>0
        BEGIN
            SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1)
            SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs))
            INSERT INTO #tmpDept (DeptID) SELECT @DeptID
        END
END

이렇게하면 하나의 부서 ID, 쉼표가있는 여러 ID 또는 쉼표와 공백이있는 여러 ID를 전달할 수 있습니다.

따라서 다음과 같은 작업을 수행 한 경우

SELECT Dept.Name
FROM Departments
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name

전달한 모든 부서 ID의 이름이 표시됩니다.

다시, 이것은 임시 테이블을 채우는 함수를 사용하여 단순화 할 수 있습니다 … 나는 주로 지루함을 없애기 위해 하나도하지 않고 수행했습니다.

-케빈 페어차일드

답변

XML을 사용할 수 있습니다.

예 : 

declare @xmlstring as  varchar(100)
set @xmlstring = '<args><arg value="42" /><arg2>-1</arg2></args>'

declare @docid int

exec sp_xml_preparedocument @docid output, @xmlstring

select  [id],parentid,nodetype,localname,[text]
from    openxml(@docid, '/args', 1)

sp_xml_preparedocument 명령 이 내장되어 있습니다.

출력이 생성됩니다. 

id  parentid    nodetype    localname   text
0   NULL        1           args        NULL
2   0           1           arg         NULL
3   2           2           value       NULL
5   3           3           #text       42
4   0           1           arg2        NULL
6   4           3           #text       -1

당신이 필요로하는 것의 전부 (더?)가 있습니다.

답변

스토어드 프로 시저를 사용하고 쉼표로 구분 된 부서 ID 목록을 전달하려는 경우 초고속 XML 메소드 : 

Declare @XMLList xml
SET @XMLList=cast('<i>'+replace(@DepartmentIDs,',','</i><i>')+'</i>' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))

모든 크레딧은 Guru Brad Schulz의 블로그로 이동합니다

답변

이거 한번 해봐:

@list_of_params varchar(20) -- value 1, 2, 5, 7, 20 

SELECT d.[Name]
FROM Department d
where @list_of_params like ('%'+ CONVERT(VARCHAR(10),d.Id)  +'%')

매우 간단합니다.

답변