Arrays de parametros en procedimientos almacenados en SQL Server

Los procedimientos almacenados de SQL Server son una gran herramienta para poder hacer parte de nuestro trabajo de acceso a datos dentro del propio servidor.

Pero cuando empezamos a crear procedimientos almacenados cada vez más y más complicados llega un momento en el que nos encontramos con la necesidad de que nuestro procedimiento reciba un número indeterminado de parámetros.

Cuando esto me ocurrió a mi recuerdo que me puse a buscar en los BOL como podía pasar un Array a mi procedimiento, pero me encontré con la imposibilidad de trabajar con Arrays en TSQL.

Así que buscando un poco más encontré un par de métodos para hacer el trabajo "a mano" y eso es lo que vamos a comentar aquí.

Una primera solución es pasar un string al procedimiento almacenado donde incluimos todos los parámetros. Dentro del procedimiento almacenado partimos nuestro string en los parámetros que lo componen y podemos trabajar con ellos.

Veamos un ejemplo en el que una serie de valores se pasan al procedimiento almacenado separados por comas y dentro del procedimiento separamos la cadena en sus parámetros constituyentes.

Vamos a pasar el string "valor1,valor2,valor3" para que el procedimiento almacenado lo descomponga en "valor1", "valor2", "valor3".

El procedimiento almacenado será el siguiente:

CREATE PROCEDURE RecibirParametros @Parametros varchar(1000)
--@Parametros es la cadena de entrada
AS
--Creamos una tabla temporal por simplificar el trabajo
--y almacenar los parametros que vayamos obteniendo
CREATE TABLE #parametros (parametro varchar(1000))
SET NOCOUNT ON
--El separador de nuestros parametros sera una ,
DECLARE @Posicion int
--@Posicion es la posicion de cada uno de nuestros separadores
DECLARE @Parametro varchar(1000)
--@Parametro es cada uno de los valores obtenidos
--que almacenaremos en #parametros
SET @Parametros = @Parametros + ','
--Colocamos un separador al final de los parametros
--para que funcione bien nuestro codigo
--Hacemos un bucle que se repite mientras haya separadores
WHILE patindex('%,%' , @Parametros) <> 0
--patindex busca un patron en una cadena y nos devuelve su posicion
BEGIN
  SELECT @Posicion =  patindex('%,%' , @Parametros)
  --Buscamos la posicion de la primera ,
  SELECT @Parametro = left(@Parametros, @Posicion - 1)
  --Y cogemos los caracteres hasta esa posicion
  INSERT INTO #parametros values (@Parametro)
  --y ese parámetro lo guardamos en la tabla temporal
  --Reemplazamos lo procesado con nada con la funcion stuff
  SELECT @Parametros = stuff(@Parametros, 1, @Posicion, '')
END
--Y cuando se han recorrido todos los parametros sacamos por pantalla el resultado
SELECT * FROM #parametros
SET NOCOUNT OFF
GO

Para probar como funciona

EXECUTE RecibirParametros 'valor1,valor2,valor3'
parametro
--------------
valor1
valor2
valor3

por supuesto que habitualmente no querremos separar la cadena de entrada en parámetros sin más, sino que queremos tratarlos de alguna manera. Pero el funcionamiento esencial es el mismo que el aquí presentado.

Como el formato XML cada vez está más de moda para transmitir información se nos puede ocurrir que los parámetros a pasar al procedimiento almacenado podrían tener ese formato, sobre todo sabiendo que el SQL Server tiene unas cuantas funciones sencillas y potentes para trabajar con datos descritos en este lenguaje.

Pues vamos a ello. En esta segunda solución la colección de parámetros vamos a pasarla como una cadena pero formateada con XML. Podemos dar el siguiente aspecto a nuestros parámetros.

<raiz>
<parametro valor="valor1"/>
<parametro valor="valor2"/>
<parametro valor="valor2"/>
</raiz>

Y los parámetros descritos con este formato serán nuestra cadena de entrada

'<raiz><parametro valor="Valor1"/><parametro valor="Valor2"/>
	<parametro valor="Valor2"/></raiz>'

El procedimiento almacenado que leerá esta cadena y nos devolverá los parámetros en una tabla (para que sea similar al anterior) es el siguiente:

CREATE PROCEDURE RecibirParametrosXML @Parametros varchar(1000)
--@Parametros es la cadena XML de entrada
AS
CREATE TABLE #Parametros (Parametro varchar(1000))
--Creamos la tabla temporal para almacenar los parámetros de salida
DECLARE @idoc INT
--Nos hace falta una variable de tipo int para referirnos al documento
--XML con el que vamos a trabajar
SET NOCOUNT ON
--Preparamos el documento con sp_xmlpreparedocument
--Podéis ver la descripcion de como funciona en los BOL
EXEC sp_xml_preparedocument @idoc OUTPUT, @Parametro
--Metemos los valores en una tabla temporal leyendolos
--del documento que hemos preparado. Para ello usamos el comando
--OpenXml. Podéis ver también como funciona en los BOL
INSERT INTO #Parametros Select * FROM
	OpenXml(@idoc,'raiz/parametro',1) with (valor Varchar(1000))
--Mostramos el resultado
SELECT * FROM #Parametros
--y liberamos la memoria ocupada
exec sp_xml_removedocument @idoc output

Y por supuesto para probarlo

EXECUTE recibirparametrosXML '<principal><parametro valor="Valor1"/>
	<parametro valor="Valor2"/><parametro valor="Valor2"/></principal>'
Parametro
------------------
Valor1
Valor2
Valor2

El resultado es el mismo que antes.

Como podemos ver la imposibilidad de pasar Arrays no es un límite si utilizamos un poco de imaginación. Ahora podemos escoger si queremos usar un método más clásico y pasar los parámetros en una cadena o nos podemos decantar por una tecnología más en boga y utilizar (de alguna manera) el lenguaje XML para pasar información a nuestro procedimiento almacenado.