Uso de procedimientos almacenados en Análisis integrado

El uso de procedimientos almacenados es una manera fácil de ahorrar tiempo al escribir con frecuencia las mismas líneas de código y también de agilizar la llamada y ejecución de la consulta deseada.

Pero, ¿qué sabe sobre los procedimientos almacenados, cómo funcionan y qué ventajas brindan a los usuarios?

En este artículo, aprenderá qué es un procedimiento almacenado, sus beneficios, cómo se diferencia en comparación con las funciones, cómo crear un procedimiento almacenado y, por último, pero no menos importante, cómo usar los procedimientos almacenados en el análisis integrado.

¿Qué es un procedimiento almacenado?

Un procedimiento almacenado es un conjunto de instrucciones SQL y otras construcciones PL/SQL almacenadas en un sistema de administración de bases de datos relacionales (RDBMS) como un grupo que puede guardar y reutilizar repetidamente. Un procedimiento almacenado puede constar de varias instrucciones SQL como SELECCIONAR, INSERTAR, ACTUALIZAR o ELIMINAR. Funcionan como una unidad y se utilizan para resolver un problema específico o realizar un conjunto de tareas relacionadas. Eso significa que si necesita una consulta SQL y la escribe repetidamente, puede guardarla como un procedimiento almacenado y luego llamarlo para realizar la consulta.

Si usa Microsoft SQL Server, puede encontrar procedimientos almacenados expandiendo el nodo Programabilidad debajo de su base de datos en el Explorador de objetos de SQL Server Management Studio.

Beneficios de usar procedimientos almacenados

Los procedimientos almacenados son uno de los temas de base de datos más utilizados y populares y, como tales, ofrecen varios beneficios. El uso de procedimientos almacenados puede simplificar y acelerar la ejecución de consultas SQL.

Por ejemplo, los procedimientos de almacenamiento pueden reducir el tráfico de red entre servidores y clientes. Esto es el resultado de que los comandos se ejecutan como un solo lote de código; por lo tanto, en lugar de enviar cada línea de código para su ejecución individualmente, solo se envía al servidor la llamada para ejecutar el procedimiento almacenado.

Estos son algunos de los otros beneficios de usar procedimientos almacenados:

mantenibilidad

Mantener un procedimiento almacenado es bastante fácil en el servidor en contraste con el mantenimiento de copias en numerosas máquinas cliente.

Además, las secuencias de comandos de los procedimientos se almacenan en una ubicación, pero los procedimientos almacenados también se pueden usar libremente en otras aplicaciones. En el caso de un cambio de definición, eso no es un problema en absoluto, ya que solo se verá afectado el procedimiento almacenado y no la aplicación que lo llama.

Eficiencia en el desempeño

Los procedimientos almacenados se compilan solo una vez y luego se pueden usar muchas veces. Esto ahorra mucho tiempo al escribir lo mismo varias veces si está realizando consultas ad-hoc y brinda una ligera ventaja de rendimiento en la primera ejecución de un procedimiento almacenado frente a la primera ejecución de una consulta ad-hoc.

Ver Reveal en acción

Incluye lógica empresarial compleja

Si bien no es el mejor lugar para la lógica empresarial compleja, un procedimiento almacenado es básicamente una llamada de función en una base de datos. Puede incluir instrucciones IF, LOOPS, comprobaciones de seguridad y mucho más para asegurarse de que se envíen los datos correctos a la llamada del procedimiento almacenado.

Si bien es algo discutible en función de los conjuntos de habilidades de su equipo, las mejores prácticas se inclinan por poner la lógica comercial para una aplicación en el lado de la aplicación. Sin embargo, los procedimientos almacenados y T-SQL son sintaxis muy poderosas para la ejecución lógica.

Fácil de modificar

Puede cambiar rápidamente una o más declaraciones en un procedimiento de almacenamiento con la ayuda del comando ALTER PROCEDURE, lo que hace que sea muy rápido y fácil actualizar los resultados enviados a la aplicación que llama. A diferencia de un componente de nivel medio que incluye lógica de negocios, que necesitaría una reimplementación potencialmente costosa, un cambio rápido a un procedimiento almacenado toma segundos.

Considere este procedimiento almacenado, donde el valor @OrdYear está codificado de forma rígida. Un desarrollador o administrador de base de datos puede actualizar la variable @OrdYear con facilidad usando el comando ALTER PROCEDURE.

ALTER PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName

Seguridad

Otro gran beneficio de usar procedimientos almacenados es la seguridad que brinda a su fuente de datos. Puede mejorar la seguridad de una aplicación utilizando procedimientos almacenados restringiendo el acceso no autorizado. Por ejemplo, puede otorgar acceso a alguien a un procedimiento que actualiza la tabla de la base de datos sin permitir el acceso a la tabla en sí. También puede usar procedimientos almacenados para "ocultar" los nombres de las tablas subyacentes del usuario final del procedimiento almacenado. Dado que la persona que llama al procedimiento almacenado solo tiene el nombre del procedimiento almacenado, no el nombre de las tablas utilizadas en el procedimiento almacenado, no podrá acceder directamente a las tablas con sus datos de forma malintencionada.

Diferencias entre procedimientos almacenados y funciones

Los procedimientos y funciones almacenados son objetos de base de datos que contienen conjuntos de instrucciones SQL para completar tareas. Y si bien a primera vista pueden parecer lo mismo, son bastante diferentes.

Estas son algunas de las cosas que diferencian los procedimientos almacenados y las funciones:

• Las funciones se pueden llamar desde un procedimiento almacenado, mientras que el procedimiento almacenado no se puede llamar desde una función.
• Una función no permite parámetros de salida, mientras que un procedimiento almacenado permite tanto parámetros de salida como de entrada.
• No puede administrar transacciones dentro de una función, pero puede hacerlo dentro de un procedimiento almacenado.
• Puede llamar a una función usando una declaración de selección pero no puede llamar a un procedimiento almacenado usando una declaración de selección.
• Una función tiene un tipo de retorno y devuelve un valor, mientras que un procedimiento almacenado no tiene un tipo de retorno y devolver un valor es opcional.
• Una función solo permite una instrucción SELECT en ella, mientras que un procedimiento almacenado permite SELECCIONAR, INSERTAR, ACTUALIZAR Y ELIMINAR.
• Un procedimiento almacenado se puede usar para leer y modificar datos, mientras que una función solo puede leerlos.
• Una función se puede incrustar en una declaración SELECT, mientras que un procedimiento almacenado no se puede utilizar en una declaración SELECT.
• Una función no puede devolver múltiples conjuntos de resultados, mientras que un procedimiento almacenado sí puede.
• Podemos usar transacciones en procedimientos almacenados, pero no están permitidas en funciones.

¿Cómo crear un procedimiento almacenado?

Crear un nuevo procedimiento almacenado es sencillo. Hay 3 pasos básicos:

1. En el Explorador de objetos de SQL Server, haga clic con el botón secundario en el nodo Procedimientos almacenados en Programabilidad.

2. En la Plantilla de procedimiento almacenado nuevo, modifique la declaración CREATE PROCEDURE para incluir las tablas, los parámetros y las opciones de consulta para devolver los datos deseados del procedimiento almacenado.

3. Haga clic en el botón Ejecutar en la barra de herramientas para compilar y guardar su procedimiento almacenado.

Hay varias herramientas para crear procedimientos almacenados, usar SQL Server Management Studio interactivo es la más fácil, ya que le brinda una manera fácil de comenzar a conectarse a muchos tipos diferentes de servidores de bases de datos, incluidos Azure y SQL Server Analysis Services.

¿Cómo utilizar procedimientos de tienda en Análisis integrado?

Al usar el SDK Reveal Embed, hay 2 formas de usar los procedimientos almacenados.

1. Cuando esté creando una nueva visualización en una base de datos que admita procedimientos almacenados, verá una lista de procedimientos almacenados que están disponibles para usted. Tratará el producto almacenado que seleccione como cualquier otra fuente de datos en Reveal, con la única diferencia de que es un procedimiento almacenado parametrizado, que puede usar en un filtro pasando variables al procedimiento almacenado.

Puede ver un video rápido de 6 minutos sobre cómo usar los procedimientos almacenados a través de la interfaz de usuario Reveal aquí:

2. Como Reveal es un SDK, también tiene un control programático completo sobre los procedimientos y parámetros almacenados a través del código. Para pasar un nombre de procedimiento almacenado, junto con los parámetros necesarios, use la función ChangeVisualizationDataSourceItemAsync. Este ejemplo demuestra pasar un procedimiento almacenado llamado CustOrderHist y pasar el parámetro CustomerID.

public async override Task<RVDataSourceItem> ChangeVisualizationDataSourceItemAsync (string userId, string dashboardId, RVVisualization visualization, RVDataSourceItem dataSourceItem) { if (dashboardId == "StoredProcNorthWind") { var msSqlDsi = dataSourceItem as RVSqlServerDataSourceItem; if (msSqlDsi?.Procedure == "CustOrderHist") { msSqlDsi.ProcedureParameters["@CustomerID"] = "AROUT"; } else { msSqlDsi.Procedure = "MyProcedure"; msSqlDsi.ProcedureParameters["@SampleParam"] = "SampleParamValue"; } return dataSourceItem; } }

Envolver

Los procedimientos almacenados son una herramienta poderosa para consultar una base de datos y devolver resultados a una aplicación de llamada. Debe considerar el uso de procedimientos almacenados para simplificar la forma en que presenta los datos a los usuarios de Reveal SDK y para simplificar la forma en que maneja los parámetros en el código de su aplicación frente al uso de consultas ad hoc prolongadas y propensas a errores.

Puede obtener más información sobre el análisis integrado Reveal y cómo funciona descargando nuestro SDK o programando una demostración rápida.