Stored Procedure SQL-XML como External Object

En esta ocasión les dejo una pequeña guía para ejecutar un query sql-xml en SQL Server, mediante un sencillo stored procedure que en GX usaremos definido como External Object. Para que tengan de referencia, este ejemplo lo hice usando GXXEv2u6, generador C# y MS Sql Server 2008 R2 sp2.

Creación del Stored Procedure

Acá les dejo el script:

CREATE PROCEDURE [dbo].[sp_dynQuery]
 @query nvarchar(2048) ,
 @outNvar nvarchar(max) OUT
AS
BEGIN
    DECLARE @parmDef nvarchar(512);
    DECLARE @queryTmp nvarchar(2048)
    DECLARE @outTmp nvarchar(max);
    SET NOCOUNT ON;
    SET @parmDef = '@out nvarchar(max) OUTPUT';
    SET @queryTmp = 'SELECT @out = CAST((' + @query + ') AS NVARCHAR(max))';
    EXECUTE sp_executesql @queryTmp, @parmDef, @out=@outTmp OUTPUT; 
    SELECT @outNvar = @outTmp;
    SET NOCOUNT OFF
    RETURN(0)
END

Verán que básicamente lo que hace el SP es ejecutar la query que le daremos por parámetros, pasando el resultado a una variable de tipo nvarchar y retornando en el parámetro de salida. Como se trata de un proc que ejecuta queries dinámicas, que eventualmente puede representar un problema de seguridad, debemos asegurar dejarlo asociado a un usuario con restricciones. Específicamente, sólo permiso para ejecutar el SP y de lectura en tablas. Para estos seteos podemos pedir ayuda a nuestro buen amigo Google, al DBA o algún crack del sql que tengamos cerca.

Para que todo resulte más cómodo, les dejo scripts para crear la tabla del ejemplo y cargarle algunos datos.

CREATE TABLE [dbo].[Developer](
 [DeveloperId] [smallint] NOT NULL,
 [DeveloperName] [varchar](64) NOT NULL,
 [DeveloperLastName] [varchar](64) NOT NULL,
 [DeveloperMail] [varchar](128) NOT NULL,
PRIMARY KEY([DeveloperId])

INSERT INTO [Developer]
([DeveloperId],[DeveloperName],[DeveloperLastName],[DeveloperMail])
VALUES
    (1,'ZERR','Angelo','angelo.zerr@gmail.com')
   ,(2,'Leclercq','Pascal','pascal.leclercq@gmail.com')

A continuación la query que pasaremos al SP desde GX:

SELECT [DeveloperName]     as devname 
      ,[DeveloperLastName] as devlastname
      ,[DeveloperMail]     as devmail
FROM [Developer]
WHERE [DeveloperId] < 3
FOR XML PATH ('developer'), ROOT ('devs')

Este ejemplo es bastante sencillo, consulta algunos campos en una tabla, pero se pueden construir cosas bastante más complejas. Aquí hay un buen post sobre el tema.

Creación de External Object

Lo siguiente es la creación del External Object que en nuestra KB representará al stored procedure. Como con cualquier objeto, lo podemos iniciar con Ctrl-N, asignando el nombre que deseamos, en este caso "dynQuery". Desde antes debemos tener preparado un Data Store, que llamaremos "StoredProcs", especial para ejecutar nuestro Stored Procedure, seteado con una cuenta de usuario que mencioné antes, la cual debe tener ciertas restricciones.

En las propiedades seleccionamos el tipo "Stored Procedure" y asignamos el datastore creado previamente.
El único método a definir será "dynQuery" y en External Name ponemos el nombre del Stored Procedure, en este caso "sp_dynQuery" (se puede ver en el script de creación del SP).
En la definición de cada parámetro debemos considerar las propiedades del Stored Procedure en cuanto a Tipo de Acceso, Tipo de Dato y Nombre.

Uso del External Object

Finalmente, en algún objeto de prueba, podemos poner la invocación al EO de la siguiente forma:

&query = 'SELECT [DeveloperName]     as devname ' +
         '      ,[DeveloperLastName] as devlastname ' +
         '      ,[DeveloperMail]     as devmail ' +
         'FROM [Developer] ' +
         'WHERE [DeveloperId] < 3 ' +
         "FOR XML PATH ('developer'), ROOT ('devs')"
csharp try{
    &result = &dynQuery.dynQuery(&query,&return)
csharp }
csharp catch ( Exception e ){
csharp     [!&error!] = e.ToString();
csharp }

La ejecución de esto, si no hay problemas, debiera darnos en la variable &return el siguiente xml:

<devs>
    <developer>
        <devname>ZERR</devname>
        <devlastname>Angelo</devlastname>
        <devmail>angelo.zerr@gmail.com</devmail>
    </developer>           
    <developer>
        <devname>Leclercq</devname>
        <devlastname>Pascal</devlastname>
        <devmail>pascal.leclercq@gmail.com</devmail>
    </developer>           
</devs>           

Nota: La "indentacion" y los saltos de linea los puse para efectos de mejor visualización.

Este tipo de solución se puede usar en innumerables situaciones. Por mencionar algo, están los reportes dinámicos que les mencioné en el post anterior.
También podemos convertir a JSON si tenemos algún User Control que lo requiera, etc., etc..