SQL Server 저장 프로시저에 어레이를 전달하는 방법
SQL Server 스토어드 프로시저에 어레이를 전달하는 방법
예를 들어, 저는 직원 명단을 가지고 있습니다.이 목록을 테이블로 사용하여 다른 테이블과 결합하고 싶습니다.그러나 직원 명단은 C#의 파라미터로 전달되어야 합니다.
SQL Server 2016(또는 그 이후)
또는 구분된 목록으로 할 수 .JSON를 사용합니다.STRING_SPLIT() ★★★★★★★★★★★★★★★★★」OPENJSON().
STRING_SPLIT():
CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';
OPENJSON():
CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SELECT value FROM OPENJSON(CONCAT('["',
REPLACE(STRING_ESCAPE(@List, 'JSON'),
',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';
여기에 더 많은 것을 적어두었습니다.
SQL Server 2008(또는 그 이후)
먼저 데이터베이스에서 다음 두 개체를 만듭니다.
CREATE TYPE dbo.IDList
AS TABLE
(
ID INT
);
GO
CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List AS dbo.IDList READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT ID FROM @List;
END
GO
이제 C# 코드를 입력해 주세요.
// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();
DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));
// populate DataTable from your List here
foreach(var id in employeeIds)
tvp.Rows.Add(id);
using (conn)
{
SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
// these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
tvparam.SqlDbType = SqlDbType.Structured;
tvparam.TypeName = "dbo.IDList";
// execute query, consume results, etc. here
}
SQL Server 2005
SQL Server 2005를 사용하는 경우에도 XML보다 분할 함수를 사용하는 것이 좋습니다.먼저 다음 함수를 만듭니다.
CREATE FUNCTION dbo.SplitInts
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT Item = CONVERT(INT, Item) FROM
( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
FROM ( SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO
이제 저장 프로시저는 다음과 같습니다.
CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ',');
END
GO
에서는 'C#'으로 만 하면 됩니다.'1,2,3,12'
테이블 값 매개 변수를 통과하는 방법은 이를 사용하는 솔루션의 유지보수를 단순화하고 XML 및 문자열 분할을 비롯한 다른 구현에 비해 성능이 향상되는 경우가 많습니다.
입력은 명확하게 정의되어 있습니다( 딜리미터가 콤마인지 세미콜론인지 아무도 추측할 필요가 없습니다).또, 스토어드 프로시저의 코드를 검사하지 않는 한, 명확하지 않은 다른 처리 기능에 의존하지 않습니다.
UDT가 아닌 사용자 정의 XML 스키마를 사용하는 솔루션과 비교하면 이 작업은 비슷한 수의 단계를 수반하지만, 제 경험상 관리, 유지보수 및 읽기가 훨씬 더 간단합니다.
많은 솔루션에서 필요한 것은 이러한 UDT(사용자 정의 유형) 중 1개 또는 몇 개뿐이며 많은 저장 프로시저에서 재사용할 수 있습니다.이 예와 마찬가지로 일반적인 요건은 ID 포인터 목록을 통과하는 것입니다.기능명은 ID가 나타내는 컨텍스트를 나타내며 유형명은 범용이어야 합니다.
볼 때, 으로써, 「동행」이라고 하는 것입니다.아이디, 이 문제에 대한 까다롭고 좋은 해결책이 있습니다..';123;434;365;' 쪽인가123,434 ★★★★★★★★★★★★★★★★★」365이 있다ID. 아래 프로시저를 호출하여 이 식을 전달하면 원하는 레코드를 가져올 수 있습니다.쉽게 이 쿼리에 "다른 테이블"을 결합할 수 있습니다.SQL Server를 사용합니다.또한 테이블 변수 또는 온도 테이블을 사용하는 것에 비해 매우 빠르고 최적화된 솔루션입니다.
CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees @List AS varchar(max)
AS
BEGIN
SELECT EmployeeID
FROM EmployeesTable
-- inner join AnotherTable on ...
where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO
저장 프로시저에는 table-value 파라미터를 사용합니다.
C#에서 전달하면 파라미터와 SQLDb 데이터형이 추가됩니다.구조화.
여기를 참조해 주세요.http://msdn.microsoft.com/en-us/library/bb675163.aspx
예:
// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
CategoriesDataTable.GetChanges(DataRowState.Added);
// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
"usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
// Execute the command.
insertCommand.ExecuteNonQuery();
}
XML 매개 변수로 전달해야 합니다.
편집: 내 프로젝트에서 아이디어를 얻을 수 있는 빠른 코드:
CREATE PROCEDURE [dbo].[GetArrivalsReport]
@DateTimeFrom AS DATETIME,
@DateTimeTo AS DATETIME,
@HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
DECLARE @hosts TABLE (HostId BIGINT)
INSERT INTO @hosts
SELECT arrayOfUlong.HostId.value('.','bigint') data
FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)
그런 다음 임시 테이블을 사용하여 테이블을 결합할 수 있습니다.데이터 무결성을 유지하기 위해 arrayOfUlong을 내장 XML 스키마로 정의했습니다만, 그렇게 할 필요는 없습니다.이 기능을 사용하는 것이 좋습니다.여기에서는 항상 긴 XML을 사용할 수 있는 간단한 코드를 보여 줍니다.
IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="arrayOfUlong">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded"
name="u"
type="xs:unsignedLong" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
END
GO
어레이의 크기나 복잡성 등 컨텍스트는 항상 중요합니다.중소규모 리스트의 경우, 여기에 기재되어 있는 몇개의 회답은 문제 없습니다.다만, 몇개의 설명이 필요합니다.
- 구분된 목록을 분할하려면 SQLCLR 기반 스플리터가 가장 빠릅니다.직접 작성하거나 CLR 함수의 SQL# 라이브러리를 다운로드하기만 하면 됩니다(제가 작성했지만 String_Split 함수와 다른 많은 함수는 완전히 무료입니다).
- XML 기반 어레이를 분할하는 것은 빠를 수 있지만 요소 기반 XML이 아닌 속성 기반 XML을 사용해야 합니다(여기에서는 @AaronBertrand의 XML 예가 가장 적합합니다).
text()XML 함수XML을 사용하여 목록을 분할하는 방법(예: 성능 분석)에 대한 자세한 내용은 Phil Factor의 "XML을 사용하여 목록을 SQL Server의 매개 변수로 전달"을 참조하십시오. - 데이터가 프로시저로 스트리밍되어 사전 구문 분석 및 강하게 입력된 테이블 변수로 표시되므로 TVP를 사용하는 것이 좋습니다(적어도 SQL Server 2008 이후를 사용하는 경우). 모든 를 에 합니다.
DataTable메모리내의 데이터를 원래의 컬렉션으로부터 카피할 때에 복제하는 것을 의미합니다.에, 「」를 해 주세요.DataTableTVP를 통과하는 방법은 대규모 데이터 세트에 대해 잘 작동하지 않습니다(즉, 잘 확장되지 않음). - XML은 단순 구분된 Int 또는 String 목록과 달리 TVP와 마찬가지로 1차원 이상의 어레이를 처리할 수 있습니다. '다행하다'처럼요.
DataTableTVP 방식에서는 XML 문서의 오버헤드를 추가로 고려해야 하므로 XML은 메모리 내의 데이터 크기를 2배 이상 확장하지 않습니다.
을 포함하여 하고 있는 증가하고 있는 에는 그 가 커집니다.IEnumerable Server(TVP SQL Server 등로입니다.DataTable(다른 방법과는 달리) 메모리 내의 수집을 복제할 필요가 없습니다.와 C#했습니다.SQL 코드 C# 코드 C# 코드입니다.
위에서 설명한 바와 같이 어레이를 문자열로 변환한 후 SQL Server 내에서 문자열을 분할하는 방법이 있습니다.
SQL Server 2016에서는 다음과 같은 문자열을 분할할 수 있습니다.
STRING_SPLIT()
임시 테이블(또는 실제 테이블)에 삽입할 수 있는 일련의 행을 반환합니다.
DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"
SELECT value FROM STRING_SPLIT(@str, ';')
결과:
가치-----1234567892462233445566
고급스러운 제품을 원하는 경우:
DECLARE @tt TABLE (
thenumber int
)
DECLARE @str varchar(200)
SET @str = "123;456;789;246;22;33;44;55;66"
INSERT INTO @tt
SELECT value FROM STRING_SPLIT(@str, ';')
SELECT * FROM @tt
ORDER BY thenumber
는 위와 같은 결과(열 이름은 "thenumber" 제외)를 제공하지만 정렬됩니다.다른 테이블과 마찬가지로 테이블 변수를 사용할 수 있으므로 원하는 경우 DB의 다른 테이블과 쉽게 결합할 수 있습니다.
는 호환성 이어야 합니다. Server 설치는 수준이어야 .STRING_SPLIT()기능을 인식합니다.다음 쿼리를 사용하여 호환성 수준을 확인할 수 있습니다.
SELECT compatibility_level
FROM sys.databases WHERE name = 'yourdatabasename';
C#을 포함한 대부분의 언어에는 배열에서 문자열을 작성하기 위해 사용할 수 있는 "join" 함수가 있습니다.
int[] myarray = {22, 33, 44};
string sqlparam = string.Join(";", myarray);
합격입니다.sqlparam위의 저장 프로시저에 대한 파라미터로 지정합니다.
이게 도움이 될 거야:) 다음 단계를 따릅니다.
쿼리 편집기를 엽니다.
복사 다음 코드를 그대로 붙여넣습니다. 문자열을 Int로 변환하는 함수를 만듭니다.
CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL ); GO다음 저장 프로시저를 만듭니다.
CREATE PROCEDURE dbo.sp_DeleteMultipleId @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); END GO를 사용하여 이 합니다.
exec sp_DeleteId '1,2,3,12'입니다.다음과 같이 어레이를 C# 문자열로 변환하여 Stored Procedure 파라미터로 전달할 수 있습니다.
int[] intarray = { 1, 2, 3, 4, 5 }; string[] result = intarray.Select(x=>x.ToString()).ToArray();SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "sp_DeleteMultipleId"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;
이렇게 하면 저장된 단일 proc 호출의 여러 행이 삭제됩니다.행운을 빌어요.
SQL 서버에서는 어레이를 지원하지 않지만 저장된 proc에 컬렉션을 전달할 수 있는 방법은 여러 가지가 있습니다.
- 데이터 테이블 사용
- XML을 사용합니다.컬렉션을 xml 형식으로 변환한 다음 저장 프로시저에 입력으로 전달합니다.
아래 링크는 도움이 될 수 있습니다.
SQL Server 2016부터는 목록을 NVARCHAR()로 가져와 OPENJSON을 사용할 수 있습니다.
DECLARE @EmployeeList nvarchar(500) = '[1,2,15]'
SELECT *
FROM Employees
WHERE ID IN (SELECT VALUE FROM OPENJSON(@EmployeeList ))
새로운 테이블 타입을 작성하기 위한 번거로움 없이 어레이를 SQL 서버에 전달하기 위한 예시와 답변을 모두 검색해 왔습니다.이 linK를 찾을 때까지, 이하와 같이 프로젝트에 적용했습니다.
--다음 코드는 Array as parameter를 취득하여 그 값을 다른 테이블에 삽입합니다.
Create Procedure Proc1
@UserId int, //just an Id param
@s nvarchar(max) //this is the array your going to pass from C# code to your Sproc
AS
declare @xml xml
set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'
Insert into UserRole (UserID,RoleID)
select
@UserId [UserId], t.value('.','varchar(max)') as [RoleId]
from @xml.nodes('//root/r') as a(t)
END
즐겁게 봐주세요
SQL Server 2016부터는 분할 문자열만 사용할 수 있습니다.
예:
WHERE (@LocationId IS NULL OR Id IN (SELECT items from Split_String(@LocationId, ',')))
CREATE TYPE dumyTable
AS TABLE
(
RateCodeId int,
RateLowerRange int,
RateHigherRange int,
RateRangeValue int
);
GO
CREATE PROCEDURE spInsertRateRanges
@dt AS dumyTable READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT tblRateCodeRange(RateCodeId,RateLowerRange,RateHigherRange,RateRangeValue)
SELECT *
FROM @dt
END
이걸 알아내는데 오랜 시간이 걸렸어요 그래서 혹시나 필요할 때를 대비해서...
이는 Aaron의 답변에서 SQL 2005 메서드를 기반으로 하며, SplitInts 함수를 사용합니다(항상 쉼표를 사용하기 때문에 구분 매개 변수를 제거했습니다).SQL 2008을 사용 중이지만 유형 데이터 세트(XSD, TableAdapters)에서 사용할 수 있는 것을 원했고 문자열 매개 변수도 이와 함께 사용할 수 있다는 것을 알고 있습니다.
나는 그의 기능을 "where in (1, 2, 3)" 타입의 절로 작동시키려 했지만, 직설적으로 운이 따르지 않았다.그래서 먼저 임시 테이블을 만들고 "where in" 대신 inner join을 했습니다.다음은 사용 예시로, 제 경우 특정 성분이 포함되지 않은 레시피 목록을 얻고자 합니다.
CREATE PROCEDURE dbo.SOExample1
(
@excludeIngredientsString varchar(MAX) = ''
)
AS
/* Convert string to table of ints */
DECLARE @excludeIngredients TABLE (ID int)
insert into @excludeIngredients
select ID = Item from dbo.SplitInts(@excludeIngredientsString)
/* Select recipies that don't contain any ingredients in our excluded table */
SELECT r.Name, r.Slug
FROM Recipes AS r LEFT OUTER JOIN
RecipeIngredients as ri inner join
@excludeIngredients as ei on ri.IngredientID = ei.ID
ON r.ID = ri.RecipeID
WHERE (ri.RecipeID IS NULL)
언급URL : https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure
'source' 카테고리의 다른 글
| SQL Server에서 날짜/시간을 자르는 방법은 무엇입니까? (0) | 2023.04.07 |
|---|---|
| Output 매개 변수를 사용하여 저장 프로시저를 실행하시겠습니까? (0) | 2023.04.07 |
| SQL Server의 'descript table'과 동등한 것은 무엇입니까? (0) | 2023.04.07 |
| Check Add 구속조건 뒤에 Check 구속조건 vs. 구속조건 추가 (0) | 2023.04.07 |
| SQL 스크립트에 오류가 있습니다.배치당 하나의 문만 허용됩니다. (0) | 2023.04.07 |