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

У меня есть процесс, который захватывает кучу записей (1000) и работает на них, и когда я закончил, мне нужно отметить большое количество из них, как обработано. Я могу указать это с большим списком идентификаторов. Я пытаюсь избежать шаблона «обновления в цикле», поэтому я хотел бы найти более эффективный способ отправить этот пакет идентификаторов в хранимую процедуру MS SQL Server 2008.

Предложение № 1 - Табличные значения. Я могу определить тип таблицы w /только поле ID и отправить в таблицу, полную идентификаторов, для обновления.

Предложение №2 - параметр XML (varchar) с OPENXML () в кубе proc.

Предложение № 3 - Разбор списков. Я бы предпочел избежать этого, если это возможно, поскольку это кажется громоздким и подверженным ошибкам.

Любые предпочтения среди них или любые идеи, которые я пропустил?

46 голосов | спросил D. Lambert 14 Jpm1000000pmFri, 14 Jan 2011 22:05:04 +030011 2011, 22:05:04

11 ответов


37

Лучшие статьи по этому вопросу - Эрланд Соммарског:

Он охватывает все варианты и очень хорошо объясняет.

Извините за непродолжительность ответа, но статья Эрлан о массивах похожа на книги Джо Селко о деревьях и других обработках SQL:)

ответил Marian 14 Jpm1000000pmFri, 14 Jan 2011 22:59:28 +030011 2011, 22:59:28
21

Весь предмет обсуждается в окончательной статье Эрланда Соммарскога: " Массивы и список в SQL Server ". Выберите вариант, который выберете.

Сводка для pre SQL Server 2008, где TVP превосходят остальные

  • CSV, разделите, как вам нравится (обычно я использую таблицу Numbers)
  • XML и анализ (лучше с SQL Server 2005 +)
  • Создайте временную таблицу на клиенте

Статья стоит прочитать в любом случае, чтобы увидеть другие методы и мысли.

Изменить: поздний ответ для огромных списков в другом месте: Передача параметров массива в хранимую процедуру

ответил gbn 19 +04002011-10-19T21:12:37+04:00312011bEurope/MoscowWed, 19 Oct 2011 21:12:37 +0400 2011, 21:12:37
19

Это замечательное обсуждение на StackOverflow , которое охватывает множество подходов. Тот, который я предпочитаю для SQL Server 2008+, - это использовать таблицу -ценовые параметры . Это, по сути, решение SQL Server для вашей проблемы - передача в список значений хранимой процедуры.

Преимущества этого подхода:

  • сделать один вызов хранимой процедуры со всеми вашими данными, переданными как 1 параметр
  • ввод таблицы структурирован и строго типизирован
  • не строковое построение /синтаксический анализ или обработка XML
  • может легко использовать ввод таблицы для фильтрации, объединения и т. д.

Однако обратите внимание: . Если вы вызываете хранимую процедуру, использующую TVP через ADO.NET или ODBC, и посмотрите, как это работает с SQL Server Profiler, вы заметите, что SQL Server получает несколько INSERT для загрузки TVP, по одному для каждой строки в TVP , за которым следует вызов процедуры. Это по дизайну . Эта партия INSERT s должна быть скомпилирована каждый раз при вызове процедуры и составляет небольшие накладные расходы. Однако даже с этими накладными расходами TVP все еще сдувают другое подходы с точки зрения производительности и удобства использования для большинства случаев использования.

Если вы хотите узнать больше, Эрланд Соммарског полный тощий о том, как работают табличные параметры и приводится несколько примеров.

Вот еще один пример, который я придумал:

  CREATE TYPE id_list AS TABLE (
    id int NOT NULL PRIMARY KEY
);
ИДТИ

CREATE PROCEDURE [dbo]. [Tvp_test] (
      @ param1 INT
    , @customer_list id_list READONLY
)
В ВИДЕ
НАЧАТЬ
    SELECT @ param1 AS param1;

    - присоединяйтесь, фильтруйте, делайте все, что хотите, с этой таблицей
    - (кроме изменения)
    ВЫБРАТЬ *
    FROM @customer_list;
КОНЕЦ;
ИДТИ

DECLARE @customer_list id_list;

INSERT INTO @customer_list (
    Я бы
)
ЦЕННОСТИ (1), (2), (3), (4), (5), (6), (7);

EXECUTE [dbo]. [Tvp_test]
      @ param1 = 5
    , @customer_list = @customer_list
;
ИДТИ

ПРОЦЕДУРА DROP dbo.tvp_test;
DROP TYPE id_list;
ИДТИ
 
ответил Nick Chammas 19 +04002011-10-19T20:59:52+04:00312011bEurope/MoscowWed, 19 Oct 2011 20:59:52 +0400 2011, 20:59:52
13

Я знаю, что опаздываю на эту вечеринку, но у меня была такая проблема в прошлом, мне пришлось отправлять до 100 тыс. номеров bigint и делать несколько тестов. Мы отправили их в двоичном формате, как изображение - это было быстрее, чем все остальное для номеров до 100 тыс..

Вот мой старый (SQL Server 2005) код:

  SELECT Number * 8 + 1 AS StartFrom,
        Номер * 8 + 8 AS MaxLen
INTO dbo.ParsingNumbers
FROM dbo.Numbers
ИДТИ

CREATE FUNCTION dbo.ParseImageIntoBIGINTs (@BIGINTs IMAGE)
RETURNS TABLE
AS RETURN
    (SELECT CAST (СУБСТРИРОВАНИЕ (@BIGINTs, StartFrom, 8) AS BIGINT) Num
      FROM dbo.ParsingNumbers
      WHERE MaxLen <= DATALENGTH (@BIGINT)
    )
ИДТИ
 

Следующий код представляет собой целые числа в двоичный блок. Здесь я изменяю порядок байтов:

  статический байт [] UlongsToBytes (ulong [] ulongs)
{
int ifrom = ulongs.GetLowerBound (0);
int ito = ulongs.GetUpperBound (0);
int l = (ito - ifrom + 1) * 8;
byte [] ret = новый байт [l];
int retind = 0;
for (int i = ifrom; i <= ito; i ++)
{
ulong v = ulongs [i];
ret [retind ++] = (байт) (v> 0x38);
ret [retind ++] = (байт) (v> 0x30);
ret [retind ++] = (байт) (v> 40);
ret [retind ++] = (байт) (v> 0x20);
ret [retind ++] = (байт) (v> 0x18);
ret [retind ++] = (байт) (v> 0x10);
ret [retind ++] = (байт) (v> 8);
ret [retind ++] = (байт) v;
}
return ret;
}
 
ответил A-K 7 FebruaryEurope/MoscowbTue, 07 Feb 2012 18:33:20 +0400000000pmTue, 07 Feb 2012 18:33:20 +040012 2012, 18:33:20
8

Я разорван между ссылкой на SO или ответом на него здесь, потому что это почти вопрос программирования. Но поскольку у меня уже есть решение, которое я использую ... Я опубликую это;)

Как это работает, вы подаете строку с разделителями-запятыми (простое разделение, не разделяет стиль CSV) в хранимую процедуру как varchar (4000), а затем загружаете этот список в эту функцию и получаете удобную таблицу назад, таблица только varchars.

Это позволяет вам отправлять значения только идентификаторов, которые вы хотите обработать, и вы можете сделать простое соединение в этой точке.

В качестве альтернативы вы можете что-то сделать с помощью CLR DataTable и подать это, но это немного больше накладных расходов для поддержки, и все понимают списки CSV.

  USE [База данных]
ИДТИ
SET ANSI_NULLS ON
ИДТИ
SET QUOTED_IDENTIFIER ON
ИДТИ

ALTER FUNCTION [dbo]. [SplitListToTable] (@list nvarchar (MAX), @delimiter nchar (1) = N ',')
      RETURNS @tbl TABLE (значение varchar (4000) NOT NULL) AS
/*
http://www.sommarskog.se/arrays-in-sql.html
Этот парень, по-видимому, парень в SQL-массивах и списках

Нужен простой нединамический способ разбить список строк на вход для сравнения

Использование, например:

DECLARE @sqlParam VARCHAR (MAX)
SET @sqlParam = 'a, b, c'

ВЫБЕРИТЕ ИЗ (

выберите 'a' как col1, '1' как col2 UNION
выберите 'a' как col1, '2' как col2 UNION
выберите 'b' как col1, '3' в качестве col2 UNION
выберите 'b' как col1, '4' в качестве col2 UNION
выберите 'c' как col1, '5' в качестве col2 UNION
выберите 'c' как col1, '6' как col2) x
WHERE EXISTS (значение SELECT FROM splitListToTable (@sqlParam, ',') WHERE x.col1 = значение)

* /
НАЧАТЬ
   DECLARE @endpos int,
           @startpos int,
           @textpos int,
           @chunklen smallint,
           @tmpstr nvarchar (4000),
           @leftover nvarchar (4000),
           @tmpval nvarchar (4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength (@list) /2
   НАЧАТЬ
      SET @chunklen = 4000 - datalength (@leftover) /2
      SET @tmpstr = @leftover + substring (@list, @textpos, @chunklen)
      SET @textpos = @textpos + @chunklen

      SET @startpos = 0
      SET @endpos = charindex (@delimiter, @tmpstr)

      WHILE @endpos> 0
      НАЧАТЬ
         SET @tmpval = ltrim (rtrim (подстрока (@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (значение) VALUES (@tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex (@delimiter, @tmpstr, @startpos + 1)
      КОНЕЦ

      SET @leftover = right (@tmpstr, datalength (@tmpstr) /2 - @startpos)
   КОНЕЦ

   INSERT @tbl (значение) VALUES (ltrim (rtrim (@leftover)))
   ВЕРНУТЬ
КОНЕЦ
 
ответил jcolebrand 14 Jpm1000000pmFri, 14 Jan 2011 22:55:02 +030011 2011, 22:55:02
5

Я регулярно получаю наборы из 1000 строк и 10000 строк, отправленных из нашего приложения для обработки различными хранимыми процедурами SQL Server.

Для удовлетворения требований к производительности мы используем TVP, но вы должны реализовать свой собственный реферат dbDataReader для преодоления некоторых проблем с производительностью в режиме обработки по умолчанию. Я не буду вдаваться в хау и кому, поскольку они не подходят для этого запроса.

Я не рассматривал обработку XML, поскольку я не нашел реализацию XML, которая остается исполненной с более чем 10 000 «строк».

Обработка списка может обрабатываться одноразовой и двумерной табличной обработкой таблиц (чисел). Мы успешно использовали их в разных областях, но хорошо управляемые ТВП более эффективны, когда их насчитывается более пары сотен «строк».

Как и во всех вариантах обработки SQL Server, вы должны сделать свой выбор на основе модели использования.

ответил Robert Miller 10 FebruaryEurope/MoscowbThu, 10 Feb 2011 04:50:14 +0300000000amThu, 10 Feb 2011 04:50:14 +030011 2011, 04:50:14
5

Наконец-то я получил шанс сделать некоторые TableValuedParameters, и они отлично работают, поэтому я собираюсь вставить весь код Lotta, который показывает, как я их использую, с образцом из моего текущего кода: (примечание: мы используем ADO.NET)

Также обратите внимание: я пишу код для службы, и у меня есть много предопределенных кодов в другом классе, но я пишу это как консольное приложение, чтобы я мог его отлаживать, поэтому я разорвал все это из консольного приложения. Извините мой стиль кодирования (например, жесткие строки соединения), поскольку это было своего рода «сборка, которую нужно выбросить». Я хотел показать, как я использую List <customObject> и легко вставлять его в базу данных в виде таблицы, которую я могу использовать в хранимой процедуре. C # и TSQL ниже:

  с использованием System;
используя System.Collections.Generic;
используя System.Data;
используя System.Data.SqlClient;
используя;

namespace a.EventAMI {
    класс Db {
        private static SqlCommand SqlCommandFactory (строка sprocName, SqlConnection con) {return new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = sprocName, CommandTimeout = 0, Connection = con}; }

        public static void Update (List <Current> currentents) {
            const string CONSTR = @ "только жесткая строка соединения во время отладки";
            SqlConnection con = новое SqlConnection (CONSTR);

            SqlCommand cmd = SqlCommandFactory ("sprocname", con);
            cmd.Parameters.Add ("@CurrentTVP", SqlDbType.Structured) .Value = Converter.GetDataTableFromIEnumerable (токи, typeof (Current)); //мой пользовательский класс конвертера

            пытаться {
                используя (con) {
                    con.Open ();
                    cmd.ExecuteNonQuery ();
                }
            } catch (Exception ex) {
                ErrHandler.WriteXML (ex);
                бросить;
            }
        }
    }
    class Current {
        public string Идентификатор {get; задавать; }
        public string OffTime {get; задавать; }
        public DateTime Off () {
            return Convert.ToDateTime (OffTime);
        }

        private static SqlCommand SqlCommandFactory (строка sprocName, SqlConnection con) {return new SqlCommand {CommandType = CommandType.StoredProcedure, CommandText = sprocName, CommandTimeout = 0, Connection = con}; }

        public static List <Current> Получить все() {
            Список & л; Ток & GT; l = новый List <Current> ();

            const string CONSTR = @ "только строка жесткого кода, когда я отлаживаю";
            SqlConnection con = новое SqlConnection (CONSTR);

            SqlCommand cmd = SqlCommandFactory ("sprocname", con);

            пытаться {
                используя (con) {
                    con.Open ();
                    используя (SqlDataReader reader = cmd.ExecuteReader ()) {
                        while (reader.Read ()) {
                            l.Add (
                                новый Current {
                                    Идентификатор = читатель [0] .ToString (),
                                    OffTime = reader [1] .ToString ()
                                });
                        }
                    }

                }
            } catch (Exception ex) {
                ErrHandler.WriteXML (ex);
                бросить;
            }

            return l;
        }
    }
}

-------------------
класс преобразователя
-------------------
использование системы;
используя System.Collections;
используя System.Data;
используя System.Reflection;

namespace a {
    public static class Converter {
        public static DataTable GetDataTableFromIEnumerable (IEnumerable aIEnumerable) {
            return GetDataTableFromIEnumerable (aIEnumerable, null);
        }

        public static DataTable GetDataTableFromIEnumerable (IEnumerable aIEnumerable, Type baseType) {
            DataTable returnTable = new DataTable ();

            if (aIEnumerable! = null) {
                //Создает цикл структуры таблицы в первом элементе списка
                object baseObj = null;

                Тип objectType;

                if (baseType == null) {
                    foreach (объект obj в aIEnumerable) {
                        baseObj = obj;
                        ломать;
                    }

                    objectType = baseObj.GetType ();
                } else {
                    objectType = baseType;
                }

                PropertyInfo [] properties = objectType.GetProperties ();

                DataColumn col;

                foreach (свойство PropertyInfo в свойствах) {
                    col = new DataColumn {ColumnName = property.Name};
                    if (свойство.PropertyType == typeof (DateTime?)) {
                        col.DataType = typeof (DateTime);
                    } else if (свойство.PropertyType == typeof (Int32?)) {
                        col.DataType = typeof (Int32);
                    } else {
                        col.DataType = property.PropertyType;}
                    returnTable.Columns.Add (col);
                }

                //Добавляет строки в таблицу

                foreach (объект objItem в aIEnumerable) {
                    DataRow row = returnTable.NewRow ();

                    foreach (свойство PropertyInfo в свойствах) {
                        Object value = property.GetValue (objItem, null);
                        if (значение! = null)
                            row [property.Name] = значение;
                        еще
                            row [property.Name] = "";
                    }

                    returnTable.Rows.Add (строка);
                }
            }
            return returnTable;
        }

    }
}

USE [База данных]
ИДТИ

SET ANSI_NULLS ON
ИДТИ
SET QUOTED_IDENTIFIER ON
ИДТИ

ALTER PROC [dbo]. [Event_Update]
    @EventCurrentTVP Event_CurrentTVP READONLY
В ВИДЕ

/************************************************* ***************
    автор cbrand
    Дата
    descrip Я попрошу вас простить мне анонимизацию, которую я здесь сделал, но надеюсь, что это поможет
    вызывающего абонента, и, таким образом, приложение
************************************************** ************** /

BEGIN TRAN Event_Update

DECLARE @DEBUG INT

SET @DEBUG = 0 /* с использованием @DEBUG> 0 * /

/*
    Замените список выдающихся записей, которые в настоящее время отключены от списка из файла
    Это означает удаление всех существующих записей (быстрее усекать и вставлять, чем удалять по соединению и вставить, да?)
* /
TRUNCATE TABLE [база данных]. [Dbo]. [Event_Current]

INSERT INTO [база данных]. [Dbo]. [Event_Current]
           ([Идентификатор]
            ,[Время отключения])
SELECT [Идентификатор]
      ,[Время отключения]
  FROM @EventCurrentTVP

IF (@ @ ERROR 0 или @DEBUG 0)
НАЧАТЬ
ROLLBACK TRAN Event_Update
КОНЕЦ
ELSE
НАЧАТЬ
COMMIT TRAN Event_Update
КОНЕЦ

USE [База данных]
ИДТИ

CREATE TYPE [dbo]. [Event_CurrentTVP] AS TABLE (
    [Идентификатор] [varchar] (20) NULL,
    [OffTime] [datetime] NULL
)
ИДТИ
 

Кроме того, я буду конструктивно критиковать мой стиль кодирования, если у вас есть это предложение (всем читателям, которые сталкиваются с этим вопросом), но, пожалуйста, держите его конструктивным;) ... Если вы действительно хотите меня, найдите меня в чате. Надеемся, что с этим фрагментом кода вы можете увидеть, как они могут использовать List <Current> , как я определил его как таблицу в db и List <T> в их приложение.

ответил jcolebrand 10 FebruaryEurope/MoscowbThu, 10 Feb 2011 20:06:40 +0300000000pmThu, 10 Feb 2011 20:06:40 +030011 2011, 20:06:40
3

Я бы либо пошел с предложением №1, либо, как альтернатива, создаю таблицу царапин, которая просто содержит обработанные идентификаторы. Вставьте в эту таблицу во время обработки, затем, как только закончите, вызовите процедуру, подобную приведенной ниже:

  BEGIN TRAN

UPDATE dt
SET обработан = 1
FROM dataTable dt
ПРИСОЕДИНИТЬСЯ обработанные файлы pi ON pi.id = dt.id;

TRUNCATE TABLE обработано

COMMIT TRAN
 

Вы будете делать много вставок, но они будут за небольшим столом, поэтому он должен быть быстрым. Вы также можете загружать свои вставки с помощью ADO.net или любого другого адаптера данных, который вы используете.

ответил Eric Humphrey - lotsahelp 14 Jpm1000000pmFri, 14 Jan 2011 22:39:14 +030011 2011, 22:39:14
2

Заголовок вопроса включает задачу передачи данных из приложения в хранимую процедуру. Эта часть исключена органом вопроса, но позвольте мне также попытаться ответить на этот вопрос.

В контексте sql-server-2008, как указано тегами, есть еще одна замечательная статья Э. Соммарскога Массивы и списки в SQL Server 2008 . Кстати, я нашел это в статье, о которой упоминал Мариан в своем ответе.

Вместо того, чтобы просто указывать ссылку, я цитирую ее список содержимого:

  • Введение
  • <Литий> Фон
  • Табличные параметры в T-SQL
  • Передача табличных параметров из ADO .NET
    • Использование списка
    • Использование DataTable
    • Использование DataReader
    • Заключительные замечания
  • Использование табличных параметров из других API
    • ODBC
    • OLE DB
    • ADO
    • LINQ и Entity Framework
    • JDBC
    • PHP
    • Perl
    • Что если ваш API не поддерживает TVP
  • Соображения по эффективности -
    • На стороне сервера
    • На стороне клиента
    • Первичный ключ или нет?
  • Благодарности и обратная связь
  • История изменений

Помимо упомянутых там техник, я чувствую, что в некоторых случаях объемная и объемная вставка заслуживают упоминания в масштабе с общим случаем.

ответил bernd_k 16 Jpm1000000pmSun, 16 Jan 2011 20:02:07 +030011 2011, 20:02:07
0
  

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

Для последней версии MS SQL 2016

С MS SQL 2016 они вводят новую функцию: SPLIT_STRING () для анализа нескольких значений.

Это может легко решить вашу проблему.

Для более ранней версии MS SQL

Если вы используете более старую версию, выполните следующие действия:

Сначала выполните одну функцию:

  ALTER FUNCTION [dbo]. [UDF_IDListToTable]
 (
    @list [varchar] (MAX),
    @Seperator CHAR (1)
  )
 RETURNS @tbl TABLE (ID INT)
 С

 ВЫПОЛНИТЬ КАК ВЫЗОВ
 В ВИДЕ
  НАЧАТЬ
    DECLARE @position INT
    DECLARE @NewLine CHAR (2)
    DECLARE @no INT
    SET @NewLine = CHAR (13) + CHAR (10)

    IF CHARINDEX (@Seperator, @list) = 0
    НАЧАТЬ
    INSERT INTO @tbl
    ЗНАЧЕНИЯ
      (
        @список
      )
КОНЕЦ
ELSE
НАЧАТЬ
    SET @position = 1
    SET @list = @list + @Seperator
    WHILE CHARINDEX (@Seperator, @list, @position) <& gt; 0
    НАЧАТЬ
        SELECT @no = SUBSTRING (
                   @список,
                   @должность,
                   CHARINDEX (@Seperator, @list, @position) - @position
               )

        IF @no <& gt; «»
            INSERT INTO @tbl
            ЗНАЧЕНИЯ
              (
                @no
              )

        SET @position = CHARINDEX (@Seperator, @list, @position) + 1
    КОНЕЦ
КОНЕЦ
ВЕРНУТЬ
КОНЕЦ
 

После этого просто передайте свою строку этой функции с разделителем.

Я надеюсь, что это будет полезно для вас. : -)

ответил Ankit Bhalala 17 Mayam17 2017, 08:45:17
-1

Используйте это для создания таблицы создания типа. простой пример для пользователя

  CREATE TYPE unit_list AS TABLE (
    ItemUnitId int,
    Количество плавающих,
    IsPrimaryUnit бит
);

ИДТИ
 CREATE TYPE спецификация_list AS TABLE (
     ItemSpecificationMasterId int,
    ItemSpecificationMasterValue varchar (255)
);

ИДТИ
 объявить @units unit_list;
 вставить в @units (ItemUnitId, Amount, IsPrimaryUnit)
  значения (12,10.50, false), 120 100.50, false), (1200 500.50, true);

 declare @spec спецификация_list;
  вставить в @spec (ItemSpecificationMasterId, temSpecificationMasterValue)
   значения (12, 'test'), (124, 'test value');

 exec sp_add_item "mytests", false, @units, @spec


//Определение процедуры
ПРОЦЕДУРА СОЗДАНИЯ sp_add_item
(
    @Name nvarchar (50),
    @IsProduct бит = false,
    @UnitsArray unit_list READONLY,
    @SpecificationsArray specification_list READONLY
)
В ВИДЕ


НАЧАТЬ
    SET NOCOUNT OFF

    print @Name;
    print @IsProduct;
    выберите * из @UnitsArray;
    выберите * из @SpecificationsArray;
КОНЕЦ
 
ответил Dinesh Vaitage 9 MaramWed, 09 Mar 2016 08:29:02 +03002016-03-09T08:29:02+03:0008 2016, 08:29:02

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132