Utilizando Arrays de parametros en Procedimientos Almacenados con Sql Server

Una característica muy solicitada por los desarrolladores en Sql Server es la de poder pasar varios valores a un procedimiento almacenado en forma de array. Desde la versión 2000 utilizaba una función de Sql que me permitía trabajar con Arrays a través de una cadena de texto delimitada por comas, normalmente los utilizaba en procesos de selección como en el ejemplo siguiente:

CREATE PROCEDURE GetCustomers @codes VARCHAR(MAX) AS
SELECT CustomerID, Name, Phone, Country
FROM Customers
WHERE CustomerID IN (SELECT * FROM [dbo].[ArrayToTable] (@param))

 
En este procedimiento el parametro @codes recibe una cadena delimitada por comas con los valores ’12,54,89,104,221,423,1239’, el sp devuelve todos los clientes con estos códigos, la definición del parametro ‘@codes’ se realiza con varchar(MAX), para que la cadena pueda almacenar la mayor cantidad de códigos posible, sin embargo varchar(MAX) tiene la limitación de 8000 caracteres y en algunas situaciones se nos puede quedar pequeño.
 
La definición de la función en Transact-Sql es la siguiente:
CREATE FUNCTION [dbo].[ArrayToTable] (@delimStr NVARCHAR(max))
RETURNS

@StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal VARCHAR(20)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @strlist NVARCHAR(max), @pos INT, @delim CHAR, @lstr NVARCHAR(max)
SET @strlist = ISNULL(@delimStr,'')
SET @delim = ','

WHILE ((len(@strlist) > 0) and (@strlist <> ''))
BEGIN
SET @pos = charindex(@delim, @strlist)

IF @pos > 0
BEGIN
SET @lstr = substring(@strlist, 1, @pos-1)
SET @strlist = ltrim(substring(@strlist,charindex(@delim, @strlist)+1, 8000))
END
ELSE
BEGIN
SET @lstr = @strlist
SET @strlist = ''
END

INSERT @StrValTable VALUES (@lstr)
END
RETURN
END

GO

Básicamente lo que hace es crear una tabla temporal conformada por un campo y cada valor en un único registro, de esta forma la consulta de arriba puede ser ejecutada sin problemas, hay que tener en cuenta que el uso de tablas temporales puede reducir el rendimiento, sin embargo es mejor esto a llamar al SP muchas veces. Podriamos tambien optar por pasar los arrays con XML y readaptar la función.

Sin embargo en la versión 2008 aparecen los User-Defined Table Type, que permiten definir la estructura de una tabla, que posteriormente puede ser utilizada para pasar valores a un SP.

clip_image001

Para el ejemplo partimos de la tabla siguiente:

clip_image002

La definición de este tipo de estructura es relativamente sencilla, similar a la creación de una tabla, el tipo anterior se definiría con la siguiente instrucción transact-sql

CREATE TYPE [dbo].[UsersTable] AS TABLE
(
[UserID] [int] NOT NULL,
[Postal] [char] NULL
PRIMARY KEY([UserID])
)
GO

Este nuevo tipo de estructura nos aporta varias ventajas, podemos definir la tabla con varios campos  y limitar el contenido del campo [UserID] utilizando primary key para que no se repitan los valores.

Una vez definida la estructura podemos utilizarla como un parámetro más.

La definición del SP que utiliza la nueva estructura es la siguiente:

CREATE PROCEDURE [dbo].[GetUsers] @users dbo.UsersTable READONLY
AS
SELECT * FROM Users WHERE UserID IN (SELECT userId from @users)

GO
 
El programa para llamar al SP desde c# podria ser similar al  ejemplo siguiente:
using System;

using System.Data;

using System.Data.SqlClient;

 

namespace ConsoleApplication1

{

    class Program

    {

        static void Main()

        {

            using (SqlConnection sqlConnection = new SqlConnection("Server=(local);Integrated Security=SSPI;Initial Catalog=Database"))

            {

                using (SqlCommand sc = new SqlCommand("dbo.GetUsers", sqlConnection))

                {

                    using (IDataReader idr = GetParams())

                    {

                        sc.Parameters.Add("@users", SqlDbType.Structured).Value = idr;

 

                        sqlConnection.Open();

                        if (sqlConnection.State == ConnectionState.Open)

                        {

                            sc.CommandType = CommandType.StoredProcedure;

                            using (SqlDataReader sqlDataReader = sc.ExecuteReader())

                            {

                                if (sqlDataReader != null && sqlDataReader.FieldCount > 0)

                                {

                                    while (sqlDataReader.Read())

                                    {

                                        Console.WriteLine(String.Format("Código {0}, Nombre {1}", sqlDataReader[0], sqlDataReader[1]));

                                    }

                                    Console.ReadLine();

                                }

                            }

                        }

                    }

                }

            }

        }

 

        public static DataTableReader GetParams()

        {

            using (DataTable dt = new DataTable("Prueba"))

            {

                dt.Columns.Add("UserID", typeof(int));

 

                DataRow dr1 = dt.NewRow();

                dr1["UserId"] = 3;

                dt.Rows.Add(dr1);

 

                DataRow dr2 = dt.NewRow();

                dr2["UserId"] = 1;

                dt.Rows.Add(dr2);

 

                DataRow dr3 = dt.NewRow();

                dr3["UserId"] = 4;

                dt.Rows.Add(dr3);

 

                dt.AcceptChanges();

 

                return dt.CreateDataReader();

            }

        }

    }

}

Limitaciones de los User Defined Table Type:

– No se pueden pasar parámetros table-valued a user-defined functions

– Las estructuras solo pueden ser indexadas para soportar valores únicos o primary keys. Sql server no mantiene estadísticas sobre parámetros table-valued.

– Los parámetros table-valued son de solo lectura en el código transact-sql, no se pueden actualizar valores de filas y no se pueden insertar datos.

– No se puede alterar o modificar el diseño de una table-valued, para conseguir esto se debe borrar y volver a crear la estructura.

No debemos olvidar que en Entity FrameWork este problema se resuelve de una forma mucho más sencilla sin la utilización de SP, como usuarios de EF debemos huir del uso de procedimientos almacenados siempre que podamos, ya que debemos abstraernos de la base de datos y habitualmente la perdida de rendimiento es inapreciable, sin embargo pueden existir algunas ocasiones en que el uso del Store Procedure esté justificado, si bien, la mayoría de las veces podemos resolverlo de forma muy sencilla tal y como demuestran este par de ejemplos.

int[] customerIds = new int[] { 1, 2, 3 };

var customers = from customer in context.CustomerSet
where customerIds.Contains(customer.Id)
select customer;

---------------------------------------------------------------------

string[] cities = new string[] { "New York", "London", "Seattle" };

var query = context.Orders.Where(c => cities.Contains(c.Customer.Address.City)).Select(o => o);

 
Y recordar, alegaros de algunas practicas, si bien funcionan, son fruto de muchos errores en las aplicaciones de hoy en dia, como muestra el siguente código:
 
SqlCommand("SELECT UserID, Name, Phone FROM Users WHERE UserID IN (2,4,5,24)", sqlConnection);

3 comentarios sobre “Utilizando Arrays de parametros en Procedimientos Almacenados con Sql Server”

  1. Hola, antes de nada agradecerte el post. Tengo una duda que quizás puedas responder:

    Desde mi programa de gestión llamo a un SP para ejecutar una consulta. Al SP le paso los parámetros de todos los campos que quiero incluir en el whera (que son los del filtro de la pantalla del programa de gestión). Cada vez que me piden añadir más campos al filtro, me obliga a modificar tanto el código del programa como el SP para añadir los nuevos campos para el where de la consulta.
    Me gustaría hacerlo más «dinámico» construyéndome todo el where desde mi programa y pasándoselo al SP como una string en un único parámetro y metiéndoselo al where. ¿Es posible hacer eso?

    Un saludo

  2. Se puede mejorar el filtro, el uso de IN() no es muy performante, si la tabla a filtrar tiene un indice para filtrar contra el array, mejor un inner join, asi el plan de ejecucion del motor trabaja mejor.

    CREATE PROCEDURE [dbo].[GetUsers] @users dbo.UsersTable READONLY
    AS
    SELECT *
    FROM Users
    Inner join (SELECT aUserId from @users) tarray
    on UserID = aUserId

    GO

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *