복붙노트

[SQL] 저장 프로 시저를 파라미터들의 배열을 전달

SQL

저장 프로 시저를 파라미터들의 배열을 전달

나는 일치 ID가 배열에 있다는 행을 제외하고 테이블의 모든 행을 삭제, 저장 프로 시저에 "ID의"의 배열을 전달해야합니다.

어떻게 그것이 가장 간단한 방법으로 할 수 있습니까?

해결법

  1. ==============================

    1.저장 프로 시저를 사용합니다 :

    저장 프로 시저를 사용합니다 :

    편집하다: 직렬화 목록 (또는 다른 것)에 대한 보완 :

    List<string> testList = new List<int>();

testList.Add(1);
testList.Add(2);
testList.Add(3);

XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);

string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());

    (XML 매개 변수와 함께 사용할 준비가) 결과 :

    <?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <int>1</int>
  <int>2</int>
  <int>3</int>
</ArrayOfInt>

    ORIGINAL POST :

    매개 변수로 XML을 전달 :

    <ids>
    <id>1</id>
    <id>2</id>
</ids>

    CREATE PROCEDURE [dbo].[DeleteAllData]
(
    @XMLDoc XML
)
AS
BEGIN

DECLARE @handle INT

EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc

DELETE FROM
    YOURTABLE
WHERE
    YOUR_ID_COLUMN NOT IN (
        SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.') 
    )
EXEC sp_xml_removedocument @handle
  2. ==============================

    2.당신이 더는 SQL Server 2008 또는를 사용하는 경우, 당신은 대신 목록 데이터 저장 프로 시저에 전달 할 때마다 직렬화 및 역 직렬화의 테이블 반환 매개 변수 (TVP)라는 것을 사용 할 수 있습니다.

    당신이 더는 SQL Server 2008 또는를 사용하는 경우, 당신은 대신 목록 데이터 저장 프로 시저에 전달 할 때마다 직렬화 및 역 직렬화의 테이블 반환 매개 변수 (TVP)라는 것을 사용 할 수 있습니다.

    간단한 스키마를 생성하여하자의 시작은 우리의 놀이터 역할을합니다 :

    CREATE DATABASE [TestbedDb]
GO


USE [TestbedDb]
GO

    /* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×?
?S[°¿Q­¥½q?_Ĭ¼Ð)3õļ%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N'db_owner', N'testbedUser'
GO


    /* Now setup the schema */
CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY );
GO

INSERT INTO dbo.Table1 (t1Id)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
    (10);
GO

    대신에 우리의 스키마 및 샘플 데이터, 우리는 지금 우리의 TVP 저장 프로 시저를 만들 준비가 :

    CREATE TYPE T1Ids AS Table (
        t1Id INT
);
GO


CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
        SET NOCOUNT ON;

        SELECT Table1.t1Id FROM dbo.Table1 AS Table1
        JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END
GO

    대신에 우리의 스키마와 API 모두, 우리는과 같이 우리의 프로그램에서 TVP 저장 프로 시저를 호출 할 수 있습니다 :

            // Curry the TVP data
        DataTable t1Ids = new DataTable( );
        t1Ids.Columns.Add( "t1Id",
                           typeof( int ) );

        int[] listOfIdsToFind = new[] {1, 5, 9};
        foreach ( int id in listOfIdsToFind )
        {
            t1Ids.Rows.Add( id );
        }
        // Prepare the connection details
        SqlConnection testbedConnection =
                new SqlConnection(
                        @"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" );

        try
        {
            testbedConnection.Open( );

            // Prepare a call to the stored procedure
            SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1",
                                                                  testbedConnection );
            findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;

            // Curry up the TVP parameter
            SqlParameter sqlParameter = new SqlParameter( "Table1Ids",
                                                          t1Ids );
            findMatchingRowsInTable1.Parameters.Add( sqlParameter );

            // Execute the stored procedure
            SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( );

            while ( sqlDataReader.Read( ) )
            {
                Console.WriteLine( "Matching t1ID: {0}",
                                   sqlDataReader[ "t1Id" ] );
            }
        }
        catch ( Exception e )
        {
            Console.WriteLine( e.ToString( ) );
        }
  /* Output:
   * Matching t1ID: 1
   * Matching t1ID: 5
   * Matching t1ID: 9
   */

    같은 엔티티 프레임 워크와 같은보다 추상적 인 API를 사용하여이 작업을 수행 할 수있는 덜 고통스러운 방법은 아마이있다. 그러나, 나는이 시간에 나 자신을 위해 볼 수있는 시간이 없어.

  3. ==============================

    3.이 최고의 소스입니다 :

    이 최고의 소스입니다 :

    http://www.sommarskog.se/arrays-in-sql.html

    링크를 사용하여 분할 기능을 만들고, 같이 사용할 수 :

    DELETE YourTable
    FROM YourTable                           d
    LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value
    WHERE s.Value IS NULL

    나는 숫자 표 방식을 선호

    이것은 당신을 위해 무엇을해야 위의 링크를 기반으로 코드입니다 ...

    내 기능을 사용하기 전에, 당신은, 당신은 단지 데이터베이스 당이 한 번 할 필요는 "도우미"테이블을 설정해야합니다 :

    CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

    루프를하지 않는 매우 빠른 당신의 문자열을 분할이 기능을 사용 :

    CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

    당신이있는 테이블로이 기능을 사용할 수 있습니다 가입 :

    SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN dbo.FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

    여기 삭제입니다 :

    DELETE YourTable
    FROM YourTable                                d
    LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue
    WHERE s.ListValue IS NULL
  4. ==============================

    4.당신이 시도 할 수 :

    당신이 시도 할 수 :

    

DECLARE @List VARCHAR(MAX)

SELECT @List = '1,2,3,4,5,6,7,8'

EXEC(
'DELETE
FROM TABLE
WHERE ID NOT IN (' + @List + ')'
)
  5. ==============================

    5.

    declare @ids nvarchar(1000)

set @ids = '100,2,3,4,5' --Parameter passed

set @ids = ',' + @ids + ','

select   *
from     TableName 
where    charindex(',' + CAST(Id as nvarchar(50)) + ',', @ids) > 0
  6. ==============================

    6.당신은 저장 프로 시저가 존재 할 것으로 예상 임시 테이블을 사용할 수 있습니다. 이 등 XML을 지원하지 않는 SQL 서버의 이전 버전에서 작동합니다

    당신은 저장 프로 시저가 존재 할 것으로 예상 임시 테이블을 사용할 수 있습니다. 이 등 XML을 지원하지 않는 SQL 서버의 이전 버전에서 작동합니다

    CREATE TABLE #temp
(INT myid)
GO
CREATE PROC myproc
AS
BEGIN
    DELETE YourTable
    FROM YourTable                    
    LEFT OUTER JOIN #temp T ON T.myid=s.id
    WHERE s.id IS NULL
END
  7. ==============================

    7.나는 XML 문자열로 ID를 전달하는 생각 하는데요, 그리고 당신은에 가입 임시 테이블에 XML 분쇄기 수, 또는 당신은 또한 쿼리 XML에 대해 직접를 sp_xml_preparedocument 및 OPENXML을 사용 할 수있다.

    나는 XML 문자열로 ID를 전달하는 생각 하는데요, 그리고 당신은에 가입 임시 테이블에 XML 분쇄기 수, 또는 당신은 또한 쿼리 XML에 대해 직접를 sp_xml_preparedocument 및 OPENXML을 사용 할 수있다.

  8. ==============================

    8.무엇 XML 데이터 유형을 사용하는 대신 배열을 전달하는 방법에 대한. 나는 더 나은 솔루션과 SQL 2005에서 잘 작동하는 것을 발견

    무엇 XML 데이터 유형을 사용하는 대신 배열을 전달하는 방법에 대한. 나는 더 나은 솔루션과 SQL 2005에서 잘 작동하는 것을 발견

  9. ==============================

    9.이와 같은 I이를 SqlCommand에 적합한 인 XElement를 같이 전달에 적합하기 때문에

    이와 같은 I이를 SqlCommand에 적합한 인 XElement를 같이 전달에 적합하기 때문에

    (죄송합니다 그것은 VB.NET입니다하지만 당신은 아이디어를 얻을)

    <Extension()>
Public Function ToXml(Of T)(array As IEnumerable(Of T)) As XElement
   Return XElement.Parse(
           String.Format("<doc>{0}</doc>", String.Join("", array.Select(Function(s) String.Concat("<d>", s.ToString(), "</d>")))), LoadOptions.None)
 End Function

    이것은이 SQL 저장 프로 시저, 단축,하지 완료입니다!

    CREATE PROCEDURE [DBO]. MYPROC]  (@blah의 XML) 같이  ... WHERE SomeID IN (SELECT doc.t.value ( '.', 문서 (t로서 N (netwerkids.nodes @에서 '내부') '/ 문서 / D')))

  10. ==============================

    10.2016 SQL 서버에서는 [와 배열을 바꿈]와 JSON 볼로 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures를 전달할 수 -as-json.aspx

    2016 SQL 서버에서는 [와 배열을 바꿈]와 JSON 볼로 http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures를 전달할 수 -as-json.aspx

  11. ==============================

    11.당신은 SQL Server에서 STRING_SPLIT 기능을 사용할 수 있습니다. 여기 문서를 확인할 수 있습니다.

    당신은 SQL Server에서 STRING_SPLIT 기능을 사용할 수 있습니다. 여기 문서를 확인할 수 있습니다.

    DECLARE @YourListOfIds VARCHAR(1000) -- Or VARCHAR(MAX) depending on what you need

SET @YourListOfIds = '1,2,3,4,5,6,7,8'

SELECT * FROM YourTable
WHERE Id IN(SELECT CAST(Value AS INT) FROM STRING_SPLIT(@YourListOfIds, ','))

    12. from https://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 테이블 행 수를 얻을 수있는 가장 효율적인 방법  (0) 2020.03.24
[SQL] 교리 2의 .. IN 하위 쿼리를 수행  (0) 2020.03.24
[SQL] 어떻게 아약스를 통해 여러 데이터 필드를 보내? [닫은]  (0) 2020.03.24
[SQL] SQL 데이터베이스의 기본 키와 같은 문자열  (0) 2020.03.24
[SQL] 어떻게 WHERE 절 (I SQL Server를 사용하고 있습니다)에서 대소 문자 구분 검색을 할까?  (0) 2020.03.24
,

티스토리툴바