AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题 / 629
Accepted
D. Lambert
D. Lambert
Asked: 2011-01-15 11:05:04 +0800 CST2011-01-15 11:05:04 +0800 CST 2011-01-15 11:05:04 +0800 CST

将数组参数传递给存储过程

  • 772

我有一个流程可以抓取一堆记录(1000 条)并对它们进行操作,完成后,我需要将其中的大量记录标记为已处理。我可以用一大串 ID 来表明这一点。我试图避免“循环更新”模式,所以我想找到一种更有效的方法将这个 ID 包发送到 MS SQL Server 2008 存储过程中。

建议 #1 - 表值参数。我可以定义一个表类型,只包含一个 ID 字段,然后发送一个充满 ID 的表来更新。

建议 #2 - proc 正文中带有 OPENXML() 的 XML 参数 (varchar)。

建议 #3 - 列表解析。如果可能的话,我宁愿避免这种情况,因为它看起来笨重且容易出错。

这些中的任何偏好,或者我错过的任何想法?

sql-server sql-server-2008
  • 12 12 个回答
  • 292917 Views

12 个回答

  • Voted
  1. Best Answer
    Marian
    2011-01-15T11:59:28+08:002011-01-15T11:59:28+08:00

    Erland Sommarskog 撰写的关于此问题的最佳文章:

    • SQL Server 2008 中的数组和列表 - 使用表值参数
    • SQL Server 2005 及更高版本中的数组和列表——当 TVP 不适用时
    • SQL Server 2000 及更早版本中的数组和列表

    他涵盖了所有选项并且解释得很好。

    很抱歉答案很短,但是 Erland 关于数组的文章就像 Joe Celko 的关于树和其他 SQL 处理的书 :)

    • 50
  2. Nick Chammas
    2011-10-20T08:59:52+08:002011-10-20T08:59:52+08:00

    There is a great discussion of this on StackOverflow that covers many approaches. The one I prefer for SQL Server 2008+ is to use table-valued parameters. This is essentially SQL Server's solution to your problem--passing in a list of values to a stored procedure.

    The advantages of this approach are:

    • make one stored procedure call with all your data passed in as 1 parameter
    • table input is structured and strongly typed
    • no string building/parsing or handling of XML
    • can easily use table input to filter, join, or whatever

    However, take note: If you call a stored procedure that uses TVPs via ADO.NET or ODBC and take a look at the activity with SQL Server Profiler, you will notice that SQL Server receives several INSERT statements to load the TVP, one for each row in the TVP, followed by the call to the procedure. This is by design (link broken), it uses an efficient insert similar to BCP (see also Dan Guzman on this). This batch of INSERTs needs to be compiled every time the procedure is called, and constitutes a small overhead. However, even with this overhead, TVPs still blow away other approaches in terms of performance and usability for the majority of use cases.

    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
    
    • 31
  3. gbn
    2011-10-20T09:12:37+08:002011-10-20T09:12:37+08:00

    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

    • 21
  4. A-K
    2012-02-08T06:33:20+08:002012-02-08T06:33:20+08:00

    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;
    }
    
    • 14
  5. jcolebrand
    2011-01-15T11:55:02+08:002011-01-15T11:55:02+08:00

    我在将您推荐给 SO 或在这里回答之间犹豫不决,因为这几乎是一个编程问题。但是因为我已经有了一个我使用的解决方案......我会发布它;)

    这个工作的方式是将逗号分隔的字符串(简单拆分,不进行 CSV 样式拆分)作为 varchar(4000) 输入存储过程,然后将该列表输入此函数并返回一个方便的表格,一张只有 varchars 的表。

    这允许您只发送您想要处理的 id 的值,然后您可以在此时进行简单的连接。

    或者,您可以使用 CLR DataTable 执行某些操作并将其输入,但这需要更多的支持,并且每个人都理解 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
    
    • 9
  6. Robert Miller
    2011-02-10T17:50:14+08:002011-02-10T17:50:14+08:00

    I regularly receive sets of 1000s of rows and 10000s of rows sent from our application to be processed by various SQL Server stored procedures.

    To meet the performance demands, we use TVPs, but you must implement your own abstract of the dbDataReader to overcome some performance issues in its default-mode of processing. I will not go into the hows and whys as they are out of scope for this request.

    I did not considered XML processing as I have not found an XML implementation which remains performant with more than 10,000 "rows".

    List processing can be handled by single-dimension and double-dimension tally (numbers) table processing. We have used successfully used these in various areas, but well-managed TVPs are more performant when there are more than a couple hundred "rows".

    As with all choices regarding SQL Server processing, you have to make your choice selection based on the usage model.

    • 5
  7. jcolebrand
    2011-02-11T09:06:40+08:002011-02-11T09:06:40+08:00

    I finally got a chance to do some TableValuedParameters and they work great, so I'm going to paste a whole lotta code that shows how I'm using them, with a sample from some of my current code: (note: we use ADO.NET)

    Also note: I'm writing some code for a service, and I've got lots of predefined code bits in the other class, but I'm writing this as a console app so I can debug it, so I ripped all this from the console app. Excuse my coding style (like hardcoded connection strings) as it was sort of "build one to throw away". I wanted to show how I use a List<customObject> and push it into the database easily as a table, that I can use in the stored procedure. C# and TSQL code below:

    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
    

    Also, I'll take constructive criticism on my coding style if you have that to offer (to all readers that come across this question) but please keep it constructive ;) ... If you really want me, find me in the chatroom here. Hopefully with this chunk of code one can see how they can use the List<Current> as I have it defined as a table in the db and a List<T> in their app.

    • 5
  8. Eric Humphrey - lotsahelp
    2011-01-15T11:39:14+08:002011-01-15T11:39:14+08:00

    我要么选择提案#1,要么创建一个只保存已处理 ID 的暂存表。在处理过程中插入该表,然后一旦完成,调用类似于下面的 proc:

    BEGIN TRAN
    
    UPDATE dt
    SET processed = 1
    FROM dataTable dt
    JOIN processedIds pi ON pi.id = dt.id;
    
    TRUNCATE TABLE processedIds
    
    COMMIT TRAN
    

    你会做很多插入,但他们会在一张小桌子上,所以它应该很快。您还可以使用 ADO.net 或您正在使用的任何数据适配器批量插入。

    • 3
  9. bernd_k
    2011-01-17T09:02:07+08:002011-01-17T09:02:07+08:00

    The question title includes the task to transmit data from an application into the stored procedure. That part is excluded by the question body, but let me try to answer this too.

    In context of sql-server-2008 as specified by the tags there is another great article by E. Sommarskog Arrays and Lists in SQL Server 2008. BTW I found it in the article Marian referred to in his answer.

    Instead of just giving the link, I quote its list of content:

    • Introduction
    • Background
    • Table-Valued Parameters in T-SQL
    • Passing Table-Valued Parameters from ADO .NET
      • Using a List
      • Using a DataTable
      • Using a DataReader
      • Final Remarks
    • Using Table-Valued Parameters from Other APIs
      • ODBC
      • OLE DB
      • ADO
      • LINQ and Entity Framework
      • JDBC
      • PHP
      • Perl
      • What If Your API Does Not Support TVPs
    • Performance Considerations
      • Server-side
      • Client-side
      • Primary Key or Not?
    • Acknowledgements and Feedback
    • Revision History

    Beyond the techniques mentioned there, I have the feeling that in some cases bulkcopy and bulk insert deserve to be mentioned to scope with the general case.

    • 2
  10. Ankit Bhalala
    2017-05-17T21:45:17+08:002017-05-17T21:45:17+08:00

    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. :-)

    • 2

相关问题

  • 死锁的主要原因是什么,可以预防吗?

  • 我在索引上放了多少“填充”?

  • 是否有开发人员遵循数据库更改的“最佳实践”类型流程?

  • 如何确定是否需要或需要索引

  • 从 SQL Server 2008 降级到 2005

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    您如何显示在 Oracle 数据库上执行的 SQL?

    • 2 个回答
  • Marko Smith

    如何选择每组的第一行?

    • 6 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    我可以查看在 SQL Server 数据库上运行的历史查询吗?

    • 6 个回答
  • Marko Smith

    如何在 PostgreSQL 中使用 currval() 来获取最后插入的 id?

    • 10 个回答
  • Marko Smith

    如何在 Mac OS X 上运行 psql?

    • 11 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Marko Smith

    将数组参数传递给存储过程

    • 12 个回答
  • Martin Hope
    Manuel Leduc PostgreSQL 多列唯一约束和 NULL 值 2011-12-28 01:10:21 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Stuart Blackler 什么时候应该将主键声明为非聚集的? 2011-11-11 13:31:59 +0800 CST
  • Martin Hope
    pedrosanta 使用 psql 列出数据库权限 2011-08-04 11:01:21 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST
  • Martin Hope
    BrunoLM Guid vs INT - 哪个更好作为主键? 2011-01-05 23:46:34 +0800 CST
  • Martin Hope
    bernd_k 什么时候应该使用唯一约束而不是唯一索引? 2011-01-05 02:32:27 +0800 CST
  • Martin Hope
    Patrick 如何优化大型数据库的 mysqldump? 2011-01-04 13:13:48 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve