Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img

Stored Procedure with array Parameters. Como pasamos variables tipo tabla (arrays) a procedimientos almacenados. SQL Server.

 Las variable tabla es una nueva característica desde la versión 2008, y como menciona el titulo de este articulo, podemos pasar este tipo de variables a procedimientos, os lo muestro con un ejemplo:

 ("Hago un inciso": Recomendando, la lectura del articulo: Stored Procedure with Input and Output Parameters. Return Values. Usar un procedimiento almacenado con parámetros de entrada y salida. SQL Server. para el correcto entendimiento de este articulo)

 

CREATE TYPE Tabla AS TABLE

( id int

, texto varchar(100) );

GO

 

create procedure demo (@t Tabla readonly)

as

begin

select * from @t

end

 

go

declare @t tabla

insert into @t values (1,'hola'),(2,'Adios')

exec demo @t


En este enlace, para mas información:
http://technet.microsoft.com/en-us/library/bb510489.aspx

 

 Pero, ¿Cómo pasar la variable tipo tabla de un procedimiento a otro?. Modificando un poco el ejemplo de antes vemos que sí es posible:


CREATE TYPE Tabla AS TABLE
( id INT
, texto VARCHAR(100) );
GO

CREATE PROC demo2 (@t Tabla readonly) AS

    SELECT  *, 'desde proc. demo2' origen
    FROM    @t
   
GO

CREATE PROCEDURE demo (@t Tabla readonly)
AS
BEGIN
    SELECT  *, 'desde proc. demo' origen
    FROM     @t
   
   
    EXEC demo2 @t
   
END

GO

DECLARE @t tabla
INSERT INTO @t VALUES (1,'hola'),(2,'Adios')
EXEC demo @t

GO

DROP PROC demo,demo2
DROP TYPE Tabla

 

Ahora bien, hay que tener en cuenta que este tipo de parámetros son de sólo lectura, así que en ese sentido pierde un poco la funcionalidad. Si lo que necesitas es modificar los datos, habría que optar por otras alternativas (tablas temporales, por ejemplo)

 

 Os muestro un método, de cómo pasar y tratar un string en un procedimiento, donde cargaremos los valores de un array:

Primero Pasaremos un string al procedimiento almacenado donde incluimos todos los parámetros. Despues, 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.

 

Hacemos lo mismo, utilizando XML:

 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.

 

 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.

 

 

 Otra opción, es crear una función que devuelva una variables de tipo tabla , dentro de un procedimiento almacenado:

 

 Algo asi como:
 
CREATE FUNCTION dbo.fnTuFunction
(
@Param1 VARCHAR(8000),
@Param2 VARCHAR(8000)
)
RETURNS @Results TABLE (intRowId INTEGER IDENTITY(1,1) , Items VARCHAR(8000))
AS
BEGIN
--Aquí pon el cuerpo...  Inserta el conjunto de resultados en la variable tabla @Results
--Las funciones de múltiples líneas se parecen, aunque de lejos, "", a procedimientos almacenados, permiten flexibilidad...
Return
END


 Esta opción, no es de mi agrado, yo siempre intento no usar funciones en mis procedimientos, y antes de usarla tiraría por el camino de apoyarte en una tabla temporal.

 

 

Fuentes:

Microsoft, msdn, programacion.com.

 

Apunte y recopilación por Norman M. Pardell

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 09:05 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Tomo I. Memoria RAM. Optimización de sistemas de 32 y 64 bits. SQL Server 2008.
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad