Вопрос по sql – Передача массива параметров в хранимую процедуру

73

Мне нужно передать массив "идентификаторов" с к хранимой процедуре, чтобы удалить все строки из таблицы, за исключением строк, которые соответствуют идентификаторам в массиве.

Как я могу сделать это самым простым способом?

Разве это не дубликатstackoverflow.com/questions/114504/…? John Saunders
@ Джон Сондерс, есть много «проходных массивов в качестве параметра» вопросы о sql server. Тем не менее, это добавило поворот, удалив все строки, кроме переданной в параметрах части вопроса. В результате я не думаю, что это дубликат. KM.
Джон Сондерс, я не знаю, что я провел поиск, но не нашел то, что искал. Есть ли проблема с этим? markiz
если вы делаете большое количество вызовов процедур и вам нужно каждый раз создавать строку xml, вы неэффективны, и чистый метод sql будет лучше. если у вас уже есть строка xml или вы выполняете мало вызовов процедур, с xml все будет в порядке. racer x

Ваш Ответ

10   ответов
0

итаю, что лучшее решение и хорошо работает в SQL 2005

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded markiz
Error: User Rate Limit Exceeded
2
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
Error: User Rate Limit Exceeded
12



DECLARE @List VARCHAR(MAX)

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

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

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
42

EDIT: Дополнение для сериализации List (или чего-либо еще):

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
Error: User Rate Limit Exceeded markiz
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
0

потому что он подходит для передачи в качестве XElement, который подходит для SqlCommand

(Извините, это 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, сокращенный, не полный!

СОЗДАНИЕ ПРОЦЕДУРЫ [dbo]. [Myproc]  (@blah xml)
КАК  ... ГДЕ SomeID IN (ВЫБЕРИТЕ doc.t.value (''. ',' ', Int' ') из @ netwerkids.nodes (N' / doc / d ') как doc (t))

1

ML, а затем вы можете измельчить XML во временную таблицу, к которой хотите присоединиться, или вы также можете запросить XML напрямую, используяsp_XML_preparedocument а такжеOPENXML.

3

которая, как ожидается, будет существовать в хранимой процедуре. Это будет работать на старых версиях SQL Server, которые не поддерживают XML и т. Д.

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
20

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

создайте функцию split, используя ссылку, и используйте ее следующим образом:

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

Я предпочитаю подход таблицы чисел

Это код, основанный на ссылке выше, который должен сделать это для вас ...

Прежде чем использовать мою функцию, необходимо настроить & quot; помощник & quot; Таблица, вам нужно сделать это только один раз для каждой базы данных:

CREA,TE 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 @[email protected]+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

Вы можете использовать эту функцию в качестве таблицы в соединении:

SELEC,T
    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
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededstackoverflow.com/questions/4227552/…Error: User Rate Limit Exceeded
56

вы можете использовать что-то, называемое табличным параметром (TVP) вместо сериализации & amp; десериализацию данных списка каждый раз, когда вы хотите передать их в хранимую процедуру.

Давайте начнем с создания простой схемы, которая будет служить нашей игровой площадкой:

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, такой как Entity Framework. Тем не менее, у меня нет времени, чтобы увидеть для себя в это время.

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded

Похожие вопросы