여러 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 사용 . .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

You would see the names of all of the department IDs that you passed in...

Again, this can be simplified by using a function to populate the temporary table... I mainly did it without one just to kill some boredom :-P

-- Kevin Fairchild

---

You could use XML.

E.g.

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) 

The command sp_xml_preparedocument is built in.

This would produce the output:

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

which has all (more?) of what you you need.

---

A superfast XML Method, if you want to use a stored procedure and pass the comma separated list of Department IDs :

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))

All credit goes to Guru Brad Schulz's Blog

---

Try This One:

@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)  +'%')

very simple.

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