Home / Questions / 71088
Accepted
Juan Manuel Palacios
Asked: 2020-05-19 06:37:10 +0800 CST 2020-05-19 06:37:10 +0800 CST

What is the maximum number of fields allowed in an Insert with a Store Procedure?

  • 772

I am reviewing an example project using MVVM and Store Procedures for DB access in SQL Server. I wanted to know if there is a maximum number of fields that can be passed to a Store Procedure from C#.

In the example they put 6 fields, but if I have a table with 30 or 40 fields (invoice, reference guide), considering that the vast majority are entered by the user, is there a better way to send them to the table?

This is the example line of code.

dc.AddProduct(p.CategoryName, p.ModelNumber, p.ModelName, p.UnitCost, p.Description, ref newProductId);
sql-server

2 Answers

  1. The Column limit in an insert is 4096 columns according to this Article.

    Columns per INSERT statement 4096

    I think you wouldn't need a "better way" to send them.

    EDIT:

    Complement with the part that says

    Bytes in source text of a stored procedure Less of batch size or 250 MB

    Y

    Parameters per stored procedure 2,100

    EDIT 2:

    Maybe Yes, there is a better way, sending an XML representation of your object to the SP and deserializing it to perform the insert.

    something like that:

    <Params>
    <type ID="1"> 
        <value> 10 </value>
    </type>
    <type ID="2"> 
        <value> abc </value>
    </type>
</Params>

    Deserialization:

        SELECT
        Type = TypeNode.value('@ID', 'int'),
        NodeValue = TypeNode.value('(value)[1]', 'varchar(50)')
    FROM
        @XML.nodes('/Params/type') AS XTbl(TypeNode)

    But in my opinion, the "traditional" way of doing it is better.

    I took this info from This Answer on SO in English, personally I can't understand much how it works but maybe it will help you.

    • 5
  2. Best Answer

    Another way you can do it is by serializing the data you want to send to the database through an XML, in this way you would only be receiving a parameter in your Stored Procedure .

    For example, having the following XML:

    <Producto>
    <ProductoId>1</ProductoId>
    <Descripcion>Descripción de algún producto</Descripcion>
    <FechaCreacion>2017-05-18</FechaCreacion>
</Producto>

    You can read it in your Stored Procedure as follows:

    CREATE PROCEDURE AddProduct (
    @contenidoXML XML
)
AS
BEGIN
    SELECT 
         Tabla.Columna.value('ProductoId[1]', 'INT')
        ,Tabla.Columna.value('Descripcion[1]', 'VARCHAR(64)')
        ,Tabla.Columna.value('FechaCreacion[1]', 'DATETIME')
    FROM @contenidoXML.nodes('//Producto') Tabla(Columna)
END

    When executing the Stored Procedure you will only have this:

    dc.AddProduct(contenidoXML);

    If you don't have a class with the definition of those 30 fields, it is possible to create the XML dynamically.

    • 5