SQL Server 배열형 매개변수 처리 Work/SQL Server2012. 4. 10. 19:05
개발을 하다 보면 1차원 배열을 받아서 테이블 형태로 가공한 뒤 처리해야 할 경우가 발생한다.
이를 처리하는 방법에 대한 정리.
Method 1: Dynamic SQL
CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (' + @OrderList + ')'
EXEC(@SQL)
END
GO
EXEC dbo.GetOrderList1 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 2: Pass...
Method 3: Parsing the comma separated values into a table variable and joining the table variable to main table
CREATE PROC dbo.GetOrderList3
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempList table
(
OrderID int
)
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
@TempList t
ON o.OrderID = t.OrderID
END
GO
EXEC dbo.GetOrderList3 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 4: Using XML
CREATE PROC dbo.GetOrderList4
(
@OrderList varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
OPENXML (@DocHandle, '/ROOT/Ord',1) WITH (OrderID int) AS x
ON o.OrderID = x.OrderID
EXEC sp_xml_removedocument @DocHandle
END
GO
EXEC dbo.GetOrderList4 '
<ROOT>
<Ord OrderID = "10248"/> <Ord OrderID = "10252"/>
<Ord OrderID = "10256"/> <Ord OrderID = "10261"/>
<Ord OrderID = "10262"/> <Ord OrderID = "10263"/>
<Ord OrderID = "10264"/> <Ord OrderID = "10265"/>
<Ord OrderID = "10300"/> <Ord OrderID = "10311"/>
<Ord OrderID = "11068"/> <Ord OrderID = "11069"/>
<Ord OrderID = "11070"/> <Ord OrderID = "11071"/>
<Ord OrderID = "11072"/> <Ord OrderID = "11073"/>
<Ord OrderID = "11074"/> <Ord OrderID = "11075"/>
<Ord OrderID = "11076"/> <Ord OrderID = "11077"/>
</ROOT>'
GO
Method 5: Using a table of numbers or pivot table, to parse the comma separated list
--Create a table called Numbers
CREATE TABLE dbo.Numbers
(
Number int PRIMARY KEY CLUSTERED
)
GO
--Insert 8000 numbers into this table (from 1 to 8000)
SET NOCOUNT ON
GO
DECLARE @CTR int
SET @CTR = 1
WHILE @CTR < 8001
BEGIN
INSERT INTO dbo.Numbers (Number) VALUES (@CTR)
SET @CTR = @CTR + 1
END
GO
--The above two steps are to be run only once. The following stored procedure uses the number table.
CREATE PROC dbo.GetOrderList5
(
@OrderList varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(OrderID, number+1, CHARINDEX(',', OrderID, number+1)-number - 1))) AS OrderID
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = Derived.OrderID
END
GO
EXEC dbo.GetOrderList5 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Method 6: Using a general purpose User Defined Function (UDF) to parse the comma separated OrderIDs
--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
CREATE PROC dbo.GetOrderList6
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
dbo.SplitOrderIDs(@OrderList) AS s
ON
o.OrderID = s.OrderID
END
GO
EXEC dbo.GetOrderList6 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'
GO
Best Solution (최상의 성능과 간단한 구현)
: 천재다...
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'A,B,C',
@delimiter = ','
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
참고 URL
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://stackoverflow.com/questions/1922191/passing-array-as-parameter-to-sql-2005-stored-procedure
