Przekazywanie tablicy parametrów do procedury składowanej

Jeśli używasz Sql Server 2008 lub lepszego, możesz użyć czegoś o nazwie parametr wartości tabeli (TVP) zamiast serializacji i deserializacji danych listy za każdym razem, gdy chcesz przekazać je do procedury składowanej.

Zacznijmy od stworzenia prostego schematu, który posłuży jako nasz plac zabaw:

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

Z naszym schematem i przykładowymi danymi, jesteśmy teraz gotowi do utworzenia naszej procedury składowanej 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

Z naszym schematem i API w miejscu, możemy wywołać TVP przechowywane procedura z naszego programu tak:

        // 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
   */

Prawdopodobnie istnieje mniej bolesny sposób, aby to zrobić przy użyciu bardziej abstrakcyjnego API, takiego jak Entity Framework. Jednak nie mam czasu, aby zobaczyć na własne oczy w tej chwili.

To jest najlepsze źródło:

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

Utwórz podzieloną funkcję używając linku i użyj go w następujący sposób:

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

Preferuję podejście do tabeli liczb

Jest to kod oparty na powyższym linku, który powinien zrobić to za Ciebie...

Zanim skorzystasz z mojej funkcji, musisz skonfigurować tabelę" helper", musisz to zrobić tylko raz w każdej bazie danych:

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

Użyj tej funkcji, aby podzielić łańcuch, który nie pętla i jest bardzo szybka:

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

Możesz użyć tej funkcji jako tabeli w połączeniu:

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

Oto twoje usunięcie:

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

Możesz spróbować tego:

DECLARE @List VARCHAR(MAX)

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

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

Możesz użyć tabeli tymczasowej, której oczekuje procedura składowana. Będzie to działać na starszych wersjach SQL Server, które nie obsługują XML itp.

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

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

Rozważyłbym przekazanie Twoich identyfikatorów jako ciąg znaków XML, a następnie mógłbyś zniszczyć XML do tabeli tymczasowej, aby połączyć się z nim, lub możesz również zapytać o XML bezpośrednio za pomocą SP_XML_PREPAREDOCUMENT i OPENXML .

Co powiesz na użycie typu danych XML zamiast przekazywania tablicy. Uważam, że lepsze rozwiązanie i działa dobrze w SQL 2005

Podoba mi się ten, ponieważ nadaje się do podania jako XElement, który jest odpowiedni dla SqlCommand

(Sorry it is VB.NET ale masz pomysł)

<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

To jest zapisany proc sql, skrócony, nie kompletny!

Utwórz procedurę [dbo].[myproc] (@blah xml)
Jako ... WHERE SomeID IN (SELECT doc.t. wartość (".', 'int') z @netwerkids.nodes (N'/doc/d') as doc (t))

W SQL Server 2016 można zawinąć tablicę za pomocą [ ] i przekazać ją jako JSON zobacz http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures-as-json.aspx{[2]