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.
