c# - 프로시저 - SQL Server 저장 프로 시저에 배열을 전달하는 방법




프로 시저 declare (6)

SQL Server 저장 프로 시저에 배열을 전달하는 방법은 무엇입니까?

예를 들어 직원 목록이 있습니다. 이 목록을 테이블로 사용하고 다른 테이블과 조인하고 싶습니다. 그러나 직원 목록은 C #에서 매개 변수로 전달되어야합니다.


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을 사용하는 솔루션의 유지 관리를 단순화하고 XML 및 문자열 분할을 비롯한 다른 구현에 비해 성능을 향상시키는 경우가 많습니다.

입력이 명확하게 정의되어 있으므로 (구분 기호가 쉼표 또는 세미콜론인지 추측 할 필요가 없음) 저장 프로 시저에 대한 코드를 검사하지 않으면 명확하지 않은 다른 처리 함수에 대한 종속성이 없습니다.

UDT 대신 사용자 정의 XML 스키마가 포함 된 솔루션과 비교할 때 이와 비슷한 단계가 있지만 내 경험상 관리, 유지 및 읽기가 훨씬 간단한 코드입니다.

많은 솔루션에서 많은 저장 프로 시저에 대해 재사용하는 이러한 UDT (사용자 정의 유형) 중 하나 또는 몇 개만 필요할 수 있습니다. 이 예제에서와 같이 일반적인 요구 사항은 ID 포인터 목록을 통과하는 것이고, 함수 이름은 ID가 나타내는 컨텍스트를 설명하고 형식 이름은 generic이어야합니다.


SQL Server에는 배열에 대한 지원이 없지만 컬렉션을 저장 프로 시저에 전달할 수있는 여러 가지 방법이 있습니다.

  1. 데이터 테이블 사용
  2. XML을 사용하여 컬렉션을 XML 형식으로 변환 한 다음 저장 프로 시저에 대한 입력으로 전달합니다.

아래 링크는 도움이 될 수 있습니다.

저장 프로 시저에 컬렉션 전달


나는 모든 예제와 SQL 서버에 배열을 전달하는 방법에 대한 답변을 통해 새로운 테이블 유형을 생성하는 번거 로움 linK 발견 할 때까지 아래에서 필자가 수행 한 프로젝트에 어떻게 적용했는지를 살펴 linK .

- 다음 코드는 매개 변수로 Array를 가져와 그 배열의 값을 다른 테이블에 삽입합니다.

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 

즐기 셨으면 좋겠습니다


내 경험에 비추어 볼 때 employeeID에서 구분 된 표현식을 작성하면이 문제에 대한 까다로운 해결 방법이 있습니다. ';123;434;365;' 와 같은 문자열 표현식 만 만들어야합니다 ';123;434;365;' in-which 123 , 434365 는 일부 employeeID입니다. 아래의 프로 시저를 호출하고이 표현식을 전달하면 원하는 레코드를 가져올 수 있습니다. 쉽게 "다른 테이블"을이 쿼리에 추가 할 수 있습니다. 이 솔루션은 모든 버전의 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

이것은 당신을 도울 것입니다. :) 다음 단계를 따르십시오.

  1. 쿼리 디자이너 열기
  2. 복사 다음 코드를 그대로 붙이면 문자열을 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
    
  3. 다음 저장 프로 시저 만들기

     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
    
  4. 이 SP 실행 exec sp_DeleteId '1,2,3,12' 사용하여 exec sp_DeleteId '1,2,3,12' 이것은 삭제하려는 Id 문자열입니다.

  5. C #에서 배열을 문자열로 변환하고 저장 프로 시저 매개 변수로 전달합니다.

    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 ;
    

이렇게하면 여러 행이 모두 삭제됩니다.


저장 프로 시저에 테이블 반환 매개 변수를 사용하십시오.

C #에서 전달하면 SqlDb.Structured 데이터 유형의 매개 변수가 추가됩니다.

여기를 참조하십시오 : 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();
}




stored-procedures