저장 프로시저에서 목록을 매개 변수로 전달하려면 어떻게 해야 합니까?

저장 프로시저에서 목록을 매개 변수로 전달하려면 어떻게 해야 합니까?

목록 이름을 반환할 사용자 ID 목록을 전달하려고 합니다.출력된 이름(COALESCE 등)을 처리할 계획이 있지만 사용자 ID 목록을 전달하는 가장 좋은 방법을 찾고 있습니다.내 프로시저의 내장은 다음과 같이 보일 것입니다.

create procedure [dbo].[get_user_names]
@user_id_list, --which would equal a list of incoming ID numbers like (5,44,72,81,126)
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in @user_id_list

@user_id_list에 대한 값을 전달하는 것이 제 주요 관심사입니다.

SQL 서버의 저장 프로시저에 값 배열을 전달하는 기본 방법은 테이블 값 매개 변수를 사용하는 것입니다.

먼저 다음과 같이 유형을 정의합니다.

CREATE TYPE UserList AS TABLE ( UserID INT );

그런 다음 저장 프로시저에서 해당 유형을 사용합니다.

create procedure [dbo].[get_user_names]
@user_id_list UserList READONLY,
@username varchar (30) output
as
select last_name+', '+first_name 
from user_mstr
where user_id in (SELECT UserID FROM @user_id_list)

저장 프로시저를 호출하기 전에 테이블 변수를 채웁니다.

DECLARE @UL UserList;
INSERT @UL VALUES (5),(44),(72),(81),(126)

마지막으로 SP에 전화합니다.

EXEC dbo.get_user_names @UL, @username OUTPUT;

제가 알기로는 테이블 값 매개 변수, 구분 목록 문자열 및 JSON 문자열의 세 가지 주요 경합이 있습니다.

2016년 이후로 구분된 경로를 원할 경우 기본 제공되는 STRING_SPLIT을 사용할 수 있습니다. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

그것이 아마도 가장 쉬운/가장 간단한/간단한 접근법일 것입니다.

또한 2016년부터 JSON은 nvarchar로 전달되어 OPENJSON과 함께 사용될 수 있습니다. https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql

스키마에서 상당히 가변적으로 전달할 수 있는 보다 구조화된 데이터 세트가 있는 경우가 가장 좋습니다.

TVP는 보다 정형화된 매개 변수를 전달하는 표준 방식이었던 것으로 보이며, 이러한 구조, 명시성 및 기본 값/유형 확인이 필요한 경우에도 여전히 좋습니다.하지만 소비자 입장에서는 좀 더 번거로울 수 있습니다.2016+가 없는 경우 기본/최적 옵션일 수 있습니다.

이러한 구체적인 고려 사항과 매개 변수의 구조에 대해 명시적으로 언급하는 것에 대한 선호 사이의 절충이라고 생각합니다. 즉, 2016+를 가지고 있더라도 문자열을 전달하고 어떻게든 구문을 구문 분석하기보다는 매개 변수의 유형/구성표를 명시적으로 설명하는 것을 선호할 수 있습니다.

Azure DB, Azure Data WH 및 SQL Server 2016에서 STRING_SPLIT을 사용하여 @ssparrow에서 설명한 것과 유사한 결과를 얻을 수 있습니다.

@sparrow의 재활용 코드

WHERE user_id IN (SELECT value FROM STRING_SPLIT( @user_id_list, ',')

값 목록을 저장 프로시저에 쉽고 효과적으로 적용하는 방법

사용해 볼 수 있습니다.

create procedure [dbo].[get_user_names]
    @user_id_list varchar(2000), -- You can use any max length

    @username varchar (30) output
    as
    select last_name+', '+first_name 
    from user_mstr
    where user_id in (Select ID from dbo.SplitString( @user_id_list, ',') )

다음은 스플릿스트링에 대한 사용자 정의 함수입니다.

Create FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

저는 다음을 통해 이 문제를 해결했습니다.

1. C#에서 String 변수를 작성했습니다.

string userId=""";

2. 나는 이 변수에 내 목록의 항목을 넣었습니다.저는 ','를 구분했습니다.

예: C#에서

userId= "5,44,72,81,126";

SQL-Server로 전송

 SqlParameter param = cmd.Parameters.AddWithValue("@user_id_list",userId);

3. Function in Convert my List된 목록 을 위해 SQL-server에 분리된 생성:NVARCHAR(Max)테이블로 이동합니다.

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

4. 기본 저장 프로시저에서 아래 명령을 사용하여 항목 목록을 사용합니다.

user_id = dbo에서 항목을 선택합니다.스플릿 Ints(@user_id_list, ',';

아래 코드를 확인해 주세요. 이 작업이 가능합니다.

@ManifestNoList      VARCHAR(MAX)


WHERE
    (
        ManifestNo IN (SELECT value FROM dbo.SplitString(@ManifestNoList, ','))
    )

이것은 나에게 완벽한 작업입니다. 이 완벽한 예는 많은 사용자 문제를 해결하기를 바랍니다.

1단계 SQL에서 다음과 같은 참조 테이블 생성

 Create TYPE dbo.tblNames
AS TABLE
(
    [Name] nvarchar(max)
);
go

create TYPE dbo.tblNamesWithCols
AS TABLE
(
    [Name] nvarchar(max)
);
go

2단계 다음과 같은 참조 테이블 매개 변수를 사용하여 저장 프로시저 생성

    create proc syTest
    @VarTbleNameList AS dbo.tblNames READONLY,
    @VarTbleNameColsList AS dbo.tblNamesWithCols READONLY,
    @VarWhereQuery nvarchar(max)
    as
    begin
    ......
......  End

**파라미터를 사용한 스토어 프로시저 호출 **

DECLARE @VarTbleList AS dbo.tblNames
INSERT INTO @VarTbleList
VALUES ( 'tblEmployes' )
INSERT INTO @VarTbleList
VALUES ( 'tblDepartments' )
INSERT INTO @VarTbleList
VALUES ( 'tblCities' )

DECLARE @VarTbleColList AS dbo.tblNamesWithCols
INSERT INTO @VarTbleColList
VALUES ( 'tblEmployes.EmployeId as empId;' )
INSERT INTO @VarTbleColList
VALUES ( 'tblEmployes.EmployeName as empName;' )
INSERT INTO @VarTbleColList
VALUES ( 'tblDepartments.DepartmentName as deptName;'  )
INSERT INTO @VarTbleColList
VALUES ( 'tblDepartments.DepartmentId as deptId;' )

EXECUTE  syTest @VarTbleList , @VarTbleColList , @VarWhereQuery ='test'

다음과 같은 간단한 '인라인' 방법을 사용하여 string_list_type 매개 변수를 구성할 수 있습니다(SQL Server 2014에서 작동).

declare @p1 dbo.string_list_type
insert into @p1 values(N'myFirstString')
insert into @p1 values(N'mySecondString')

저장된 proc를 실행할 때 사용하는 예:

exec MyStoredProc @MyParam=@p1

올바른 방법은 사용자 정의 데이터 유형을 생성하는 것입니다.

CREATE TYPE [dbo].[IntArray] AS TABLE
(
    [ID] [INT] NULL
)

그런 다음 다음 사용자 정의 데이터 유형을 사용할 수 있습니다.

CREATE OR ALTER PROCEDURE [dbo].[sp_GetUserNames]
(
    @userIds [IntArray] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        "Name" = u.LastName + ', ' + u.FirstName
    FROM dbo.User u
    JOIN @userIds uid ON u.Id = uid.Id;
END

용도:

@DECLARE @result TABLE
(
    Name NVARCHAR(max)
);

@DECLARE @ids [IntArray] = SELECT x.userId FROM dbo.sometable x;

SET @result = EXECUTE [dbo].[sp_GetUserNames] @userIds = @ids;

SELECT * FROM @result;

다음을 사용할 수 있습니다.

select last_name+', '+first_name 
from user_mstr
where ',' + @user_id_list + ',' like '%,' + convert(nvarchar, user_id) + ',%'

언급URL : https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure