Passando parâmetros de array para um procedimento armazenado

Os melhores artigos de sempre sobre este assunto são de Erland Sommarskog:

• Matrizes e listas no SQL Server 2008 - usando parâmetros com valor de tabela
• Arrays e listas no SQL Server 2005 e além - quando os TVPs não cortam
• Matrizes e listas no SQL Server 2000 e anteriores

Ele cobre todas as opções e explica muito bem.

Desculpe a brevidade da resposta, mas o artigo de Erland sobre Arrays é como os livros de Joe Celko sobre árvores e outros deleites SQL :)

Há uma grande discussão sobre isso no StackOverflow que abrange muitas abordagens. O que eu prefiro para o SQL Server 2008+ é usar parâmetros com valor de tabela . Esta é essencialmente a solução do SQL Server para o seu problema - passando uma lista de valores para um procedimento armazenado.

As vantagens desta abordagem são:

• faça uma chamada de procedimento armazenado com todos os seus dados passados ​​como 1 parâmetro
• a entrada da tabela é estruturada e fortemente tipada
• sem construção/análise de strings ou manipulação de XML
• pode usar facilmente a entrada da tabela para filtrar, juntar ou qualquer outra coisa

No entanto, observe: Se você chamar um procedimento armazenado que usa TVPs via ADO.NET ou ODBC e observar a atividade com o SQL Server Profiler, notará que o SQL Server recebe várias INSERTinstruções para carregar o TVP, uma para cada linha no TVP , seguido da chamada para o procedimento. Isso é por design (link quebrado) , ele usa uma inserção eficiente semelhante ao BCP (veja também Dan Guzman sobre isso). Esse lote de INSERTs precisa ser compilado toda vez que o procedimento é chamado e constitui uma pequena sobrecarga. No entanto, mesmo com essa sobrecarga, os TVPs ainda eliminam outras abordagens em termos de desempenho e usabilidade para a maioria dos casos de uso.

If you want to learn more, Erland Sommarskog has the full skinny on how table-valued parameters work and provides several examples.

Here is another example I concocted:

    CREATE TYPE id_list AS TABLE (
        id int NOT NULL PRIMARY KEY
    );
    GO
    
    CREATE PROCEDURE [dbo].[tvp_test] (
          @param1           INT
        , @customer_list    id_list READONLY
    )
    AS
    BEGIN
        SELECT @param1 AS param1;
        
        -- join, filter, do whatever you want with this table 
        -- (other than modify it)
        SELECT *
        FROM @customer_list;
    END;
    GO
    
    DECLARE @customer_list id_list;
    
    INSERT INTO @customer_list (
        id
    )
    VALUES (1), (2), (3), (4), (5), (6), (7);
    
    EXECUTE [dbo].[tvp_test]
          @param1 = 5
        , @customer_list = @customer_list
    ;
    GO
    
    DROP PROCEDURE dbo.tvp_test;
    DROP TYPE id_list;
    GO

The entire subject is discussed on the definitive article by Erland Sommarskog: "Arrays and List in SQL Server". Take your pick of which version to choose.

Summary, for pre SQL Server 2008 where TVPs trump the rest

• CSV, split how you like (I generally use a Numbers table)
• XML and parse (better with SQL Server 2005+)
• Create a temporary table on the client

The article is worth reading anyway to see other techniques and thinking.

Edit: late answer for huge lists elsewhere: Passing array parameters to a stored procedure

I know I am late for this party, but I had such a problem in the past, having to send up to 100K bigint numbers, and did a few benchmarks. We ended up sending them in binary format, as an image - that was faster than everything else for up to 100K numbers.

Here is my old (SQL Server 2005) code:

SELECT  Number * 8 + 1 AS StartFrom ,
        Number * 8 + 8 AS MaxLen
INTO    dbo.ParsingNumbers
FROM    dbo.Numbers
GO

CREATE FUNCTION dbo.ParseImageIntoBIGINTs ( @BIGINTs IMAGE )
RETURNS TABLE
AS RETURN
    ( SELECT    CAST(SUBSTRING(@BIGINTs, StartFrom, 8) AS BIGINT) Num
      FROM      dbo.ParsingNumbers
      WHERE     MaxLen <= DATALENGTH(@BIGINTs)
    )
GO

The following code is packing integers into a binary blob. I am reversing the order of bytes here:

static byte[] UlongsToBytes(ulong[] ulongs)
{
int ifrom = ulongs.GetLowerBound(0);
int ito   = ulongs.GetUpperBound(0);
int l = (ito - ifrom + 1)*8;
byte[] ret = new byte[l];
int retind = 0;
for(int i=ifrom; i<=ito; i++)
{
ulong v = ulongs[i];
ret[retind++] = (byte) (v >> 0x38);
ret[retind++] = (byte) (v >> 0x30);
ret[retind++] = (byte) (v >> 40);
ret[retind++] = (byte) (v >> 0x20);
ret[retind++] = (byte) (v >> 0x18);
ret[retind++] = (byte) (v >> 0x10);
ret[retind++] = (byte) (v >> 8);
ret[retind++] = (byte) v;
}
return ret;
}

Estou dividido entre encaminhá-lo para SO ou respondê-lo aqui, porque isso é quase uma questão de programação. Mas como já tenho uma solução eu uso... vou postar isso ;)

A maneira como isso funciona é que você alimenta uma string delimitada por vírgulas (divisão simples, não faz divisões no estilo CSV) no procedimento armazenado como um varchar (4000) e, em seguida, alimenta essa lista nessa função e obtém uma tabela útil de volta, uma tabela de apenas varchars.

Isso permite que você envie os valores apenas dos ids que deseja processar e pode fazer uma junção simples nesse ponto.

Alternativamente, você pode fazer algo com um CLR DataTable e alimentá-lo, mas isso é um pouco mais de sobrecarga para suportar e todos entendem as listas CSV.

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[splitListToTable] (@list      nvarchar(MAX), @delimiter nchar(1) = N',')
      RETURNS @tbl TABLE (value     varchar(4000)      NOT NULL) AS
/*
http://www.sommarskog.se/arrays-in-sql.html
This guy is apparently THE guy in SQL arrays and lists 

Need an easy non-dynamic way to split a list of strings on input for comparisons

Usage like thus:

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

SELECT * FROM (

select 'a' as col1, '1' as col2 UNION
select 'a' as col1, '2' as col2 UNION
select 'b' as col1, '3' as col2 UNION
select 'b' as col1, '4' as col2 UNION
select 'c' as col1, '5' as col2 UNION
select 'c' as col1, '6' as col2 ) x 
WHERE EXISTS( SELECT value FROM splitListToTable(@sqlParam,',') WHERE x.col1 = value )

*/
BEGIN
   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
   BEGIN
      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
      BEGIN
         SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (value) VALUES(@tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex(@delimiter, @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   INSERT @tbl(value) VALUES (ltrim(rtrim(@leftover)))
   RETURN
END

Recebo regularmente conjuntos de milhares de linhas e milhares de linhas enviadas de nosso aplicativo para serem processadas por vários procedimentos armazenados do SQL Server.

Para atender às demandas de desempenho, usamos TVPs, mas você deve implementar seu próprio resumo do dbDataReader para superar alguns problemas de desempenho em seu modo padrão de processamento. Não vou entrar nos comos e porquês, pois estão fora do escopo desta solicitação.

Não considerei o processamento de XML, pois não encontrei uma implementação de XML que permaneça com mais de 10.000 "linhas".

O processamento de listas pode ser tratado pelo processamento de tabela de contagem (números) de dimensão única e de dimensão dupla. Nós os usamos com sucesso em várias áreas, mas os TVPs bem gerenciados têm melhor desempenho quando há mais de algumas centenas de "linhas".

Como acontece com todas as opções relacionadas ao processamento do SQL Server, você deve fazer sua seleção com base no modelo de uso.

Finalmente tive a chance de fazer alguns TableValuedParameters e eles funcionam muito bem, então vou colar um monte de código que mostra como estou usando-os, com uma amostra de alguns dos meus códigos atuais: (nota: usamos ADO .INTERNET)

Observe também: estou escrevendo algum código para um serviço e tenho muitos bits de código predefinidos na outra classe, mas estou escrevendo isso como um aplicativo de console para que eu possa depurá-lo, então extraí tudo isso de o aplicativo de console. Desculpe meu estilo de codificação (como strings de conexão codificadas), pois era uma espécie de "construir um para jogar fora". Eu queria mostrar como eu uso a List<customObject>e o empurro no banco de dados facilmente como uma tabela, que posso usar no procedimento armazenado. Código C# e TSQL abaixo:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using a;

namespace a.EventAMI {
    class Db {
        private static SqlCommand SqlCommandFactory(string sprocName, SqlConnection con) { return new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = sprocName, CommandTimeout = 0, Connection = con }; }

        public static void Update(List<Current> currents) {
            const string CONSTR = @"just a hardwired connection string while I'm debugging";
            SqlConnection con = new SqlConnection( CONSTR );

            SqlCommand cmd = SqlCommandFactory( "sprocname", con );
            cmd.Parameters.Add( "@CurrentTVP", SqlDbType.Structured ).Value = Converter.GetDataTableFromIEnumerable( currents, typeof( Current ) ); //my custom converter class

            try {
                using ( con ) {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            } catch ( Exception ex ) {
                ErrHandler.WriteXML( ex );
                throw;
            }
        }
    }
    class Current {
        public string Identifier { get; set; }
        public string OffTime { get; set; }
        public DateTime Off() {
            return Convert.ToDateTime( OffTime );
        }

        private static SqlCommand SqlCommandFactory(string sprocName, SqlConnection con) { return new SqlCommand { CommandType = CommandType.StoredProcedure, CommandText = sprocName, CommandTimeout = 0, Connection = con }; }

        public static List<Current> GetAll() {
            List<Current> l = new List<Current>();

            const string CONSTR = @"just a hardcoded connection string while I'm debugging";
            SqlConnection con = new SqlConnection( CONSTR );

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

            try {
                using ( con ) {
                    con.Open();
                    using ( SqlDataReader reader = cmd.ExecuteReader() ) {
                        while ( reader.Read() ) {
                            l.Add(
                                new Current {
                                    Identifier = reader[0].ToString(),
                                    OffTime = reader[1].ToString()
                                } );
                        }
                    }

                }
            } catch ( Exception ex ) {
                ErrHandler.WriteXML( ex );
                throw;
            }

            return l;
        }
    }
}

-------------------
the converter class
-------------------
using System;
using System.Collections;
using System.Data;
using 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 ) {
                //Creates the table structure looping in the in the first element of the list
                object baseObj = null;

                Type objectType;

                if ( baseType == null ) {
                    foreach ( object obj in aIEnumerable ) {
                        baseObj = obj;
                        break;
                    }

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

                PropertyInfo[] properties = objectType.GetProperties();

                DataColumn col;

                foreach ( PropertyInfo property in properties ) {
                    col = new DataColumn { ColumnName = property.Name };
                    if ( property.PropertyType == typeof( DateTime? ) ) {
                        col.DataType = typeof( DateTime );
                    } else if ( property.PropertyType == typeof( Int32? ) ) {
                        col.DataType = typeof( Int32 );
                    } else {
                        col.DataType = property.PropertyType;
                    }
                    returnTable.Columns.Add( col );
                }

                //Adds the rows to the table

                foreach ( object objItem in aIEnumerable ) {
                    DataRow row = returnTable.NewRow();

                    foreach ( PropertyInfo property in properties ) {
                        Object value = property.GetValue( objItem, null );
                        if ( value != null )
                            row[property.Name] = value;
                        else
                            row[property.Name] = "";
                    }

                    returnTable.Rows.Add( row );
                }
            }
            return returnTable;
        }

    }
}

USE [Database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[Event_Update]
    @EventCurrentTVP    Event_CurrentTVP    READONLY
AS

/****************************************************************
    author  cbrand
    date    
    descrip I'll ask you to forgive me the anonymization I've made here, but hope this helps
    caller  such and thus application
****************************************************************/

BEGIN TRAN Event_Update

DECLARE @DEBUG INT

SET @DEBUG = 0 /* test using @DEBUG <> 0 */

/*
    Replace the list of outstanding entries that are still currently disconnected with the list from the file
    This means remove all existing entries (faster to truncate and insert than to delete on a join and insert, yes?)
*/
TRUNCATE TABLE [database].[dbo].[Event_Current]

INSERT INTO [database].[dbo].[Event_Current]
           ([Identifier]
            ,[OffTime])
SELECT [Identifier]
      ,[OffTime]
  FROM @EventCurrentTVP

IF (@@ERROR <> 0 OR @DEBUG <> 0) 
BEGIN
ROLLBACK TRAN Event_Update
END
ELSE
BEGIN
COMMIT TRAN Event_Update
END

USE [Database]
GO

CREATE TYPE [dbo].[Event_CurrentTVP] AS TABLE(
    [Identifier] [varchar](20) NULL,
    [OffTime] [datetime] NULL
)
GO

Além disso, aceitarei críticas construtivas ao meu estilo de codificação, se você tiver isso a oferecer (a todos os leitores que se depararem com essa pergunta), mas, por favor, mantenha-as construtivas ;) ... Se você realmente me quer, encontre-me na sala de bate-papo aqui . Espero que com este pedaço de código seja possível ver como eles podem usar o List<Current>como eu o defini como uma tabela no banco de dados e um List<T>em seu aplicativo.

Eu iria com a proposta nº 1 ou, como alternativa, criaria uma tabela de rascunho que contém apenas os IDs processados. Insira nessa tabela durante o processamento e, depois de concluído, chame um proc semelhante ao abaixo:

BEGIN TRAN

UPDATE dt
SET processed = 1
FROM dataTable dt
JOIN processedIds pi ON pi.id = dt.id;

TRUNCATE TABLE processedIds

COMMIT TRAN

Você fará muitas inserções, mas elas serão em uma mesa pequena, então deve ser rápido. Você também pode agrupar suas inserções usando ADO.net ou qualquer adaptador de dados que esteja usando.

O título da pergunta inclui a tarefa de transmitir dados de um aplicativo para o procedimento armazenado. Essa parte é excluída pelo corpo da pergunta, mas deixe-me tentar responder isso também.

No contexto do sql-server-2008, conforme especificado pelas tags, há outro ótimo artigo de E. Sommarskog Arrays and Lists in SQL Server 2008 . BTW, encontrei no artigo que Marian se referiu em sua resposta.

Em vez de apenas dar o link, cito sua lista de conteúdo:

• Introdução
• Fundo
• Parâmetros com valor de tabela em T-SQL
• Passando parâmetros com valor de tabela do ADO .NET
  • Usando uma lista
  • Usando uma tabela de dados
  • Usando um DataReader
  • Considerações finais
• Como usar parâmetros com valor de tabela de outras APIs
  • ODBC
  • OLE DB
  • ALVOROÇO
  • LINQ e Entity Framework
  • JDBC
  • PHP
  • Perl
  • E se sua API não suportar TVPs
• Considerações de desempenho
  
  • Lado do servidor
  • Lado do cliente
  • Chave primária ou não?
• Agradecimentos e Feedback
• Histórico de Revisão

Além das técnicas mencionadas lá, tenho a sensação de que em alguns casos bulkcopy e bulk insert merecem ser mencionados no escopo do caso geral.

Passing array parameters to a stored procedure

For MS SQL 2016 latest version

With MS SQL 2016 they introduce a new function : SPLIT_STRING() to parse multiple values.

This can solve your problem easily.

For MS SQL Older Version

If you are using older version, than follow this step:

First Make one function:

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

 EXECUTE AS CALLER
 AS
  BEGIN
    DECLARE @position INT
    DECLARE @NewLine CHAR(2) 
    DECLARE @no INT
    SET @NewLine = CHAR(13) + CHAR(10)

    IF CHARINDEX(@Seperator, @list) = 0
    BEGIN
    INSERT INTO @tbl
    VALUES
      (
        @list
      )
END
ELSE
BEGIN
    SET @position = 1
    SET @list = @list + @Seperator
    WHILE CHARINDEX(@Seperator, @list, @position) <> 0
    BEGIN
        SELECT @no = SUBSTRING(
                   @list,
                   @position,
                   CHARINDEX(@Seperator, @list, @position) - @position
               )

        IF @no <> ''
            INSERT INTO @tbl
            VALUES
              (
                @no
              )

        SET @position = CHARINDEX(@Seperator, @list, @position) + 1
    END
END
RETURN
END

After making this, Just pass your string to this function with separator.

I hope this is helpful to you. :-)