여러 ID 값을 받아들이는 T-SQL 저장 프로시저

여러 ID 값을 받아들이는 T-SQL 저장 프로시저

스토어드 프로시저에 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 사용.SQL Server 2005 이후를 참조하십시오.NET 언어만.
• XML. 많은 행을 삽입하는 데 매우 적합합니다. SELECT의 경우 오버킬이 될 수 있습니다.
• 숫자의 표단순한 반복 방법보다 높은 성능/복잡성.
• 고정 길이 요소길이를 고정하면 구분된 문자열보다 속도가 향상됩니다.
• 숫자의 함수테이블에서 가져오는 것이 아니라 함수에서 생성되는 숫자 테이블 및 고정 길이의 변형입니다.
• CTE(Recursive Common Table Expression)입니다.SQL Server 2005 이상.복잡하지 않고 반복적인 방법보다 뛰어난 퍼포먼스.
• 다이내믹 SQL속도가 느릴 수 있으며 보안에 영향을 미칩니다.
• List as Many Parameters 전달지루하고 오류가 발생하기 쉽지만 단순합니다.
• 매우 느린 방법.charindex, patindex 또는 LIKE를 사용하는 메서드.

이 모든 옵션 간의 단점에 대해 알아보려면 기사를 읽을 만큼 충분히 추천할 수 없습니다.

네, 현재 솔루션은 SQL 주입 공격을 받기 쉽습니다.

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

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

값을 많이 사용할 경우 먼저 임시 테이블에 값을 쓰는 방법을 고려할 수 있습니다.그럼 그냥 평상시처럼 동참하면 돼

이렇게 하면 파싱은 한 번만 할 수 있습니다.

'분할' 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의 이름이 표시됩니다.

이 경우에도 함수를 사용하여 임시 테이블을 채우는 것으로 단순화할 수 있습니다.나는 주로 지루함을 달래기 위해 그것을 없이 했다:-P

-- 케빈 페어차일드

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의 블로그에 있습니다.

언급URL : https://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values