Cómo pasar un número variable de argumentos a un procedimiento almacenado de SQL Server usando XML, evitando el uso de SQL dinámico (sp_executesql)
SQL Server 2000 incluye un nuevo conjunto de funciones para el manejo de datos en formato XML. Por ejemplo, es posible devolver datos al cliente en formato XML mediante la habitual sentencia SELECT junto a la cláusula FOR XML, o transformar los datos contenidos en un documento XML en un conjunto de filas (registros) con los que trabajar de manera clásica (con sentencias Transact-SQL como SELECT, INSERT, UPDATE o DELETE) mediante OpenXML.
Una de las posibilidades que nos ofrecen éstas nuevas funciones es la implementación de argumentos variables en los procedimientos almacenados. OpenXML permite trabajar con los datos contenidos en un documento XML (en realidad, una cadena de texto plano) como si de un conjunto de registros se tratase, de manera que podemos agregar un argumento a un procedimiento almacenado que reciba una cadena XML donde se incluyan todos los parámetros que este procedimiento necesite. De ésta manera, evitamos el uso de SQL dinámico (sp_executesql) para ejecutar instrucciones Transact-SQL generadas en tiempo de ejecución y los inconvenientes que su uso acarrea (como la peligrosa inyección de código SQL) además de obtener un mejor rendimiento (las instrucciones ejecutadas con EXEC o sp_executesql no son compiladas hasta el momento de la ejecución y el plan de ejecución generado por el optimizador de consultas raramente es reutilizado).
¿Cómo funciona OpenXML?
OpenXML transforma una cadena de texto con estructura XML en un conjunto de registros estándar (como, por ejemplo, el conjunto de registros devuelto por una sentencia SELECT). Para obtener correctamente éstos datos, debemos especificar algunos parámetros de OpenXML; los más comunes son:
OPENXML(num_doc int, patron_filas nvarchar) [WITH (esquema_filas)]
· num_doc: Controlador interno del documento (un número entero obtenido mediante sp_xml_preparedocument) · patron_filas: Patrón XPath que identifica cuáles de los nodos del documento XML se procesarán · esquema_filas: Definición del tipo de datos que contienen los nodos del documento XML, en formato [nombre_columna, tipo_dato_columna]
Veamos un pequeño ejemplo donde se obtiene un conjunto de registros de dos columnas a partir de un documento XML con la siguiente estructura:
<paises> <pais IdPais="1" Nombre="Francia"/> <pais IdPais="2" Nombre="Italia"/> </paises>
Fig.1 – Ejemplo de obtención de registros mediante OpenXML
Con ésta característica de SQL Server podemos resolver un problema muy común en gran número de aplicaciones de gestión: permitir al usuario seleccionar los registros que desea que aparezcan en un determinado informe. Si nuestra tabla tiene un campo que identifique inequívocamente un registro, sólo tenemos que “armar” la cadena XML que incluya todos éstos identificadores y pasársela al procedimiento almacenado, para su uso en las correspondientes sentencias SELECT internas.
¿Por qué no usar SQL dinámico?La solución habitual a éste problema es el uso de SQL dinámico: generar una cadena de texto con las sentencias necesarias para obtener los datos que necesitamos y ejecutarlas mediante EXEC() o sp_executesql; por ejemplo:
DECLARE @ids varchar(20) SET @ids = '5, 6, 7, 8' EXEC('SELECT * FROM Empleados WHERE IdEmpleado IN (' + @ids + ')')
Sin duda, ésta es una característica muy potente de SQL Server, pero conviene usarla con cuidado y tener en cuenta sus principales inconvenientes, entre los que destacaría los siguientes:
· Las sentencias SQL se ejecutan en un alcance distinto del procedimiento que las llamó, por lo que no pueden tener acceso a sus parámetros y variables locales. · Las tablas temporales creadas en las sentencias SQL se eliminan al terminar éstas (por lo que no es posible trabajar con ellas desde el procedimiento que las ejecuta); las opciones SET establecidas en estas sentencias tampoco afectan al procedimiento externo
Para más información sobre este tema, recomiendo encarecidamente la lectura del siguiente artículo de Erland Sommarskog (Microsoft® MVP SQL Server) traducido por Simon Hayes:
Las virtudes y maldades del SQL dinámico http://www.hayes.ch/sql/sql_dinamico.html
Caso práctico:
Veamos como el soporte para XML de SQL Server puede ayudarnos en un caso real. Supongamos que tenemos una tabla Empleados con varios cientos de registros, y una aplicación de gestión donde el usuario puede hacer altas, bajas y modificaciones de éstos datos, así como generar informes basados en ellos. Tenemos un formulario donde el usuario puede seleccionar los empleados que desea que aparezcan en un determinado informe:
Fig.2 – Formulario de ejemplo para la selección de los empleados a incluir en el informe
Vamos a desarrollar un procedimiento almacenado que reciba un argumento XML (en realidad, una cadena de texto varchar) que contendrá la información con la cual el procedimiento podrá identificar y devolver los registros que el usuario desea imprimir.
¡¡Manos a la obra!!:
Vamos a crear una base de datos en nuestro servidor y una tabla “Empleados” que utilizaremos para nuestro proyecto de prueba:
/* Crear la base de datos PruebasXML */ CREATE DATABASE PruebasXML GO USE PruebasXML GO /* Crear la tabla Empleados */ CREATE TABLE Empleados ( IdEmpleado int IDENTITY(1, 1) PRIMARY KEY, Nombre varchar(20), Apellido1 varchar(20), Apellido2 varchar(20), FechaNacimiento datetime ) GO /* Insertar algunos registros */ INSERT INTO Empleados VALUES ('Jorge', 'Martínez', 'García', '19760703') INSERT INTO Empleados VALUES ('Pedro', 'López', 'Valle', '19680319') INSERT INTO Empleados VALUES ('Luis', 'Iglesias', 'González', '19961107') INSERT INTO Empleados VALUES ('Alfredo', 'Muñiz', 'Suárez', '19790901') INSERT INTO Empleados VALUES ('Sergio', 'Rodríguez', 'Alvarez', '19810430') INSERT INTO Empleados VALUES ('Rafael', 'Fuentes', 'Aranda', '19661022') INSERT INTO Empleados VALUES ('Miguel', 'Arias', 'Gómez', '19700101') GO
Una vez creadas la base de datos y la tabla e insertados algunos registros, la vista desde el “Analizador de consultas” de SQL Server sería la siguiente:
Fig.3 – Vista previa de la tabla Empleados en el Analizador de consultas de SQL Server
Crearemos ahora el procedimiento almacenado que devolverá un determinado conjunto de empleados, según la cadena XML recibida:
CREATE PROCEDURE spInformeEmpleados @TextoXML varchar(2000) AS SET NOCOUNT ON DECLARE @DocXML int /* Cargar el documento XML del texto del argumento @TextoXML */ EXEC sp_xml_preparedocument @DocXML output, @TextoXML /* Seleccionar los registros de la tabla Empleados cuyo campo IdEmpleado esté incluido en el nodo <empleados/empleado/IdEmpleado> del documento XML */ SELECT * FROM Empleados WHERE IdEmpleado IN ( SELECT IdEmpleado FROM OpenXML(@DocXML, 'empleados/empleado') WITH (IdEmpleado int) ) ORDER BY Apellido1, Apellido2 /* Destruir el documento XML y liberar memoria */ EXEC sp_xml_removedocument @DocXML SET NOCOUNT OFF
Éste procedimiento almacenado recibe, en su argumento @TextoXML, una cadena XML que define (a través de su identificador IdEmpleado) los registros que deseamos que sean devueltos por el procedimiento. Esta cadena XML tendrá un formato similar al siguiente:
<empleados> <empleado IdEmpleado="1"/> <empleado IdEmpleado="3"/> <empleado IdEmpleado="4"/> <empleado IdEmpleado="6"/> </empleados>
El siguiente paso será crear una pequeña aplicación en Visual Basic 6.0 y ADO que haga uso de éste procedimiento almacenado como origen de datos para un informe. En el evento Load del formulario (Fig.1) cargamos los empleados, leyéndolos de la tabla Empleados y mostrándolos en un control ListView:
Private Sub Form_Load() Dim Cn As ADODB.Connection, Rs As ADODB.Recordset Set Cn = New ADODB.Connection Set Rs = New ADODB.Recordset Cn.Open "Provider=SQLOLEDB.1;Initial Catalog=PruebasXML;Data Source=SERVIDORSQL" Rs.Open "SELECT * FROM Empleados ORDER BY Apellido1, Apellido2", Cn, adOpenForwardOnly While Not Rs.EOF '// Cargar el control ListView con los empleados de la base de datos With lvwEmpleados.ListItems.Add(, , Rs.Collect("Nombre"), , 1) .Tag = Rs.Collect("IdEmpleado") .SubItems(1) = Rs.Collect("Apellido1") .SubItems(2) = Rs.Collect("Apellido2") .SubItems(3) = Rs.Collect("FechaNacimiento") End With Rs.MoveNext Wend Rs.Close: Set Rs = Nothing Cn.Close: Set Cn = Nothing End Sub
En el evento Click del botón “Aceptar” (btnAceptar) llamamos al procedimiento almacenado (mediante un objeto ADODB.Command) enviando como único argumento la cadena XML que contiene los identificadores de los empleados seleccionados:
Private Sub btnAceptar_Click() If lvwEmpleados.SelectedItem Is Nothing Then MsgBox "Debe seleccionar al menos un empleado para el informe", vbCritical Else MostrarInforme GenerarXML End If End Sub
Private Sub MostrarInforme(ByVal strXML As String) Dim Cn As ADODB.Connection, Cm As ADODB.Command Set Cn = New ADODB.Connection Set Cm = New ADODB.Command With Cn .Mode = adModeRead .CursorLocation = adUseClient .Open "Provider=SQLOLEDB.1;Initial Catalog=PruebasXML;Data Source=SERVIDORSQL" End With '// Llamar al procedimiento almacenado spInformeEmpleados, con la cadena XML como argumento With Cm .ActiveConnection = Cn .CommandText = "spInformeEmpleados" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@TextoXML", adVarChar, adParamInput, 2000, strXML) MostrarVistaPreviaInforme .Execute End With Cn.Close: Set Cn = Nothing End Sub
Private Function GenerarXML() As String Dim Li As MSComctlLib.ListItem, strXML As String '// Generar la cadena XML según el campo IdEmpleado (almacenado en la '// propiedad Tag de cada elemento del ListView) For Each Li In lvwEmpleados.ListItems If Li.Checked Then strXML = strXML & "<empleado IdEmpleado=""" & Li.Tag & """/>" Next Li GenerarXML = "<empleados>" & strXML & "</empleados >" End Function
Private Sub MostrarVistaPreviaInforme(ByRef Rs As ADODB.Recordset) Set DataReport1.DataSource = Rs DataReport1.Show vbModal, frmEmpleados Rs.Close: Set Rs = Nothing End Sub
El informe resultante, utilizando un diseñador DataReport de Visual Basic 6.0, sería el siguiente:
Fig.4 – Vista previa del informe en un DataReport de Visual Basic 6.0
|
