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

Question:

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 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, referral guide), considering that the vast majority are entered by the user, will there be a better way to send them to the table?

This is the line of code for the example.

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

Answer:

Another way you can do it is by serializing the data you want to send to the database by means of 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.

Scroll to Top