Power Query: Función para ejecutar un store procedure SQL

El día de hoy veremos a detalle una manera en que podemos crear una función que ejecutará un procedimiento SQL, lo cual nos pasará un parámetro. Usaremos para ello power query y comenzaremos con el store procedure.

Store Procedure SQL a Power Query

Mi base de datos contiene un procedimiento simple que al proporcionarle nombre y apellido nos arrojará todos los registros de nomina del personal.

Y este procedimiento puede ser ejecutado con el siguiente SQL:

EXECUTE HumanResources.uspGetEmployeesTest @LastName = 'Norman', @FirstName = 'Laura'

Ahora toca crear el query inicial, el cual ejecutará un procedimiento con un parámetro fijo que será pasado al procedimiento usando el SQL de la sección anterior.

Para ello vamos añadir una nueva fuente de SQL Server. Llenaremos los campos de Server y Database y daremos click en las opciones avanzadas para revelar el campo de SQL statement, en donde introduciremos el SQL statement para ejecutar el procedimiento.

Daremos click en OK y después en Transform Data para editar el query. Cuando el query sea abierto veremos que solo tiene un paso llamado Source. Lo siguiente será crear el parámetro y la función.

Parámetros en Power Query para SQL

Luego de crear el query necesitamos un parámetro para que almacene el valor que será pasado al procedimiento. En la pestaña de Inicio seleccionaremos «Manage Parameters – New Parameter». Le daremos un nombre al parámetro y seleccionaremos su tipo como Text y finalmente un valor:

Luego de esto necesitaremos editar el paso de Source del query para usar el parámetro de arriba. Es importante que el número de comillas («) sea el correcto, ya que necesitaremos dos si las comillas («) se encuentran dentro de una secuencia.

let
    Source = Sql.Database(
        "DESKTOP-2L4RA5U\MSSQLSERVER01",
         "AdventureWorks2019", 
         [Query=
            "EXECUTE HumanResources.uspGetEmployeesTest @FirstName = '"
            & FirstName
            & "', @LastName = '"
            & LastName
            & "'"])
in
    Source

Cuando alteramos el query puede que nos salte un aviso preguntando por permisos para correr el query. De momento dejaremos que corra el query, esto lo veremos a detalle más adelante.

Luego de que hayamos incluído el parámetro el query estará ya listo para ser convertido a una función. Daremos click derecho en el query y seleccionaremos «Create Function», tras lo cual solo introduciremos un nombre para la función. Como ya vimos antes, el query y el parámetro serán movidos a un grupo con la nueva función.

Esta función puede ser llamada en una tabla de centros de costos para que nos dé todos los gastos relacionados tal y como podemos ver a continuación:

Seguridad al incovar query

Como ya dijimos antes, cuando invocamos el query nos saltará un aviso preguntando por permisos para correr un query de base de datos nativa. Esta es una precaución que nos avisa que algún SQL está siendo ejecutado, lo cual podría hacer cambios a la base de datos.

Cuando damos click en «Editar Permisos» se nos mostrará el SQL que está siendo ejecutado para que podamos revisarlo con cuidado.

Esta petición para permisos puede ser desactivada. Si damos click en Archivo en el menú de inicio y luego vamos a Opciones > Seguridad, la primera opción en el diálogo es sobre los Queries de Bases de Datos Nativas, por defecto la opción para requerir aprobación se encontrará marcada. Hay que tener en cuenta que este cambio se aplicará para todos los queries.

Como podemos ver, los procedimientos almacenados en SQL son una gran forma de hacer uso de las base de datos para crear los queries que necesitemos para buscar datos usando nuestros propios parámetros. Desde luego esto requerirá algo de trabajo extra para la base de datos, pero también tiene bastante potencial para funciones que ejecuten un procedimiento SQL.

Cualquier duda, nos leemos abajo.