Procedimientos almacenados en SQL | Una visión general

En este blog, analizaremos los procedimientos almacenados en SQL que puede utilizar para guardar un conjunto de código y usarlo repetidamente cuando lo necesite. Los procedimientos almacenados son similares a las vistas. Sin embargo, puede realizar operaciones de tabla comunes como DROP, TRUNCATE, DELETE, etc. con procedimientos almacenados que no puede realizar con vistas.

Los procedimientos almacenados también están precompilados para que se ejecuten más rápido que las vistas. También minimiza la cantidad de datos enviados al servidor de la base de datos.

Tabla de contenido

• Sintaxis para procedimientos almacenados en SQL: sin parámetros
• Sintaxis para procedimientos almacenados en SQL: con parámetros
• Ejemplo de escenario de procedimientos almacenados en SSMS
• Segundo escenario de muestra
• Tercer escenario de muestra
• Ubicación de los procedimientos almacenados guardados en SQL
• Crear un procedimiento almacenado en SQL con parámetros
• Creación de un procedimiento almacenado con un valor predeterminado
• Conclusión

Sintaxis para procedimientos almacenados en SQL: sin parámetros

Para crear un procedimiento almacenado sin parámetros, debe comenzar con la función CREAR . Luego agregue la función PROC , escriba el nombre del procedimiento justo después y agregue la función AS .

Crear un procedimiento almacenado en es similar a crear tablas y vistas. Sin embargo, la diferencia está en el proceso de obtención de datos.

Por ejemplo, si queremos obtener datos de una vista, usamos " SELECT * FROM view_name ". Por otro lado, para los procedimientos almacenados, usamos EXEC, que significa "ejecutar", y luego escribimos el nombre del procedimiento almacenado después.

Una vez que ejecutamos un procedimiento almacenado , también mostrará la información según las instrucciones o comandos que haya agregado.

Sintaxis para procedimientos almacenados en SQL: con parámetros

Pasemos a . El proceso es similar al ejemplo anterior que demostré. La única diferencia es que debe incluir los parámetros antes de la función AS y también el tipo de datos justo después.

También puede utilizar el parámetro que ha declarado en la creación del procedimiento almacenado en sus comandos. Esto es para que ahorre tiempo ingresando múltiples valores. Por ejemplo, en lugar de escribir manualmente IDcliente1 , IDCliente2 , etc., utilizamos @custid o el parámetro para evitar tener un conjunto largo de código.

Tenga en cuenta que al usar o incluir un parámetro, es importante usar el símbolo @ . También puede incluir tantos parámetros como desee, no solo uno. Así es como ejecutamos procedimientos almacenados con parámetros.

Como puede notar, también usamos el parámetro con un valor igual a 1 al ejecutar el procedimiento almacenado. Esto traerá solo la información de custid1 .

Ejemplo de escenario de procedimientos almacenados en SSMS

Veamos más ejemplos de procedimientos almacenados en SQL. En primera instancia, voy a crear un procedimiento almacenado de muestra usando el comando resaltado a continuación.

Para la convención de nomenclatura en la creación de un procedimiento almacenado, generalmente usamos "usp" o "sp" para indicar que se trata de un procedimiento almacenado. Si se pregunta qué significa "usp", simplemente significa procedimiento almacenado definido por el usuario. 

A continuación, escribiremos consultas para que el procedimiento almacenado las procese cuando se ejecute.

Las consultas del ejemplo anterior simplemente eliminarán la tabla denominada dbo.stageOrders . Luego volverá a crear el dbo.stageOrders con los datos de la tabla Sales.SalesOrderHeader . 

Vamos a crear este procedimiento almacenado resaltando el siguiente código y haciendo clic en el botón Ejecutar .

Después de eso, debería ver un mensaje como este.

Luego, ahora ejecutaremos el procedimiento almacenado ejecutando EXEC usp_TEST .

Tras la ejecución del procedimiento almacenado usp_TEST , dbo.stageOrders ahora debería tener los datos de Sales.SalesOrderHeader . Verifiquemos el contenido de dbo.stageOrders ejecutando el siguiente comando.

Como resultado, estos son los datos que creamos en dbo.stageOrders en función de los comandos que agregamos a nuestro procedimiento almacenado.

Segundo escenario de muestra

Pasemos a otro ejemplo. Esta vez no quiero quitar la tabla dbo.stageOrders y volver a crear la misma tabla con datos provenientes de la tabla Sales.SalesOrderHeader .

En cambio, solo quiero que se muestren los datos de la tabla Sales.SalesOrderHeader cada vez que ejecute el procedimiento almacenado " usp_TEST " .

Para realizar esto, necesito realizar cambios en el procedimiento almacenado creado recientemente mediante la instrucción ALTER .

En este ejemplo, utilizamos la declaración ALTER que se usa para modificar nuestro procedimiento almacenado. También puede aplicar esto a vistas y tablas. Una vez que ejecutamos el código en la captura de pantalla anterior, debería cambiar las consultas que procesará nuestro procedimiento almacenado.

Esta vez no recreará ninguna tabla como hicimos antes. Si ejecutamos " usp_TEST ", solo traerá los datos de la tabla Sales.SalesOrderHeader .

Tercer escenario de muestra

Hagamos otro ejemplo. Esta vez, reutilizaremos la tabla dbo.stageOrders que teníamos en nuestro primer ejemplo de creación de un procedimiento almacenado en SSMS . Comenzaremos con este ejemplo seleccionando la tabla dbo.stageOrders. 

Los resultados al seleccionar todos los datos de dbo.stageOrders deberían ser así.

Ahora vaciemos la tabla dbo.stageOrders ejecutando usp_TEST . Podemos hacer eso agregando el siguiente comando.

Después de agregar el comando TRUNCATE TABLE , necesitamos actualizar usp_TEST .

Después de eso, debería mostrarse un mensaje que dice que los comandos se completaron con éxito . Luego ejecutaremos usp_TEST nuevamente.

Al ejecutar usp_TEST , mostrará todos los registros de Sales.SalesOrderHeader y también vaciará la tabla  dbo.stageOrders .

Para verificar si la tabla dbo.stageOrders está vacía, debemos seleccionarla usando el siguiente comando y ejecutar.

Al ejecutar el código anterior, podemos ver que la tabla dbo.stageOrders ahora está vacía. Esto se debe al comando TRUNCATE TABLE que hemos usado para actualizar usp_TEST .

Ubicación de los procedimientos almacenados guardados en SQL

Si desea ver dónde se guarda el procedimiento almacenado en SQL , vaya al panel Explorador de objetos en el lado izquierdo y haga clic en el ícono " + " antes de la base de datos en la que está trabajando.

A continuación, haga clic con el botón derecho en Programabilidad y seleccione Actualizar .

Expanda la carpeta o grupo Programabilidad haciendo clic en el icono " + ". Luego expanda el grupo Procedimientos almacenados haciendo el mismo paso. Dentro del grupo Procedimientos almacenados, debería ver dbo.usp_TEST .

Si desea verificar qué comandos o consultas está realizando un procedimiento almacenado específico en SQL, puede hacer clic derecho en un procedimiento almacenado y seguir los pasos en la captura de pantalla a continuación.

Después de eso, abrirá el procedimiento almacenado en una pestaña diferente donde podrá ver los comandos proporcionados en él. Así es como se ve dbo.usp_TEST al abrirlo.

Como puede ver, hay algunos comandos predeterminados antes de la instrucción CREATE . Simplemente puede eliminar eso si lo desea. 

Ahora sabe cómo verificar qué comandos está ejecutando un procedimiento almacenado en SQL .

Crear un procedimiento almacenado en SQL con parámetros

A continuación, vamos a crear un procedimiento almacenado con parámetros. Por ejemplo, vamos a usar el siguiente código para crear un nuevo procedimiento almacenado.

En el código de ejemplo, utilicé el mismo proceso de creación de un procedimiento almacenado llamado usp_GetCustomer . Luego agregué un parámetro que es @CustomerID con un tipo de entrada de INT .

Tenga en cuenta que una vez que haya agregado un parámetro al crear un procedimiento almacenado, siempre debe proporcionar un parámetro cada vez que vaya a ejecutar un comando.

Veamos qué sucede si ejecutamos usp_GetCustomer sin proporcionar un parámetro.

Al ejecutar usp_GetCustomer sin un parámetro, apareció un mensaje de error. Así quedaría si ejecutamos usp_GetCustomer con un parámetro.

Con un parámetro proporcionado, podemos obtener un resultado adecuado al ejecutar nuestro procedimiento almacenado .

Creación de un procedimiento almacenado con un valor predeterminado

Si desea evitar recibir un mensaje de error al ejecutar un procedimiento almacenado con un parámetro, puede establecer un valor predeterminado que servirá como parámetro predeterminado.

Por ejemplo, vamos a crear un procedimiento almacenado llamado usp_GetOrdersByYear .

Luego agregaré un parámetro @OrderYear con el tipo de entrada " INT " y un valor predeterminado que es igual a 2011 .

Si ejecutamos usp_GetOrdersByYear sin proporcionar un parámetro, mostrará registros con el año 2011 .

Por otro lado, si ejecutamos usp_GetOrdersByYear con 2014 como parámetro dado, debería mostrar registros con el año 2014 .

Así es como utiliza los procedimientos almacenados en sus tareas diarias de gestión de datos.

Cláusula HAVING en funciones agregadas de SQL
Función ISNULL SQL en declaraciones de caso
Extracción de datos SQL usando OFFSET y FETCH

Conclusión

Teniendo todo en cuenta, ha aprendido qué es un procedimiento almacenado en SQL y su propósito. También hemos discutido la declaración ALTER que se usa para hacer cambios o actualizaciones en un procedimiento almacenado actual.

Además, aprendió que existen diferentes métodos para crear procedimientos almacenados en SQL y aprendió cómo evitar recibir errores al ejecutar un procedimiento almacenado proporcionando un valor predeterminado.

Lo que es más importante, ha aprendido a utilizar procedimientos almacenados para almacenar conjuntos de comandos para evitar la ejecución repetida de conjuntos largos de código. Como recordatorio final, no olvide utilizar el símbolo “ @ ” al proporcionar un parámetro.

Mis mejores deseos,

Hafiz