Stored Procedure en SQL Server: ¿Cómo crear, modificar y ejecutar procedimientos almacenados en SQL Server?

Para comprender esta clase a la perfección, te recomiendo que des un repaso a estos vídeos, no olvides en suscribirte a mi canal de YouTube:

• Modelo relacional
• Álgebra relacional
• Lenguaje SQL

⚠️ NOTA: recuerda que SQL Server utiliza el lenguaje Transact-SQL (T-SQL) el cual es una variante mejorada del lenguaje SQL, por lo tanto, el tema de Stored Procedure lo intentaré hacer lo más apegado a SQL, pero, si hay que profundizar, lo haré tomando en cuenta la sintaxis que proporciona Transact-SQL.

STORED PROCEDURE en SQL Server: ¿Cómo utilizar los procedimientos almacenados en SQL Server?

Los procedimientos almacenados o Stored Procedure (SP) son una gran opción, en algunas ocasiones, para la manipulación de nuestros datos en una base de datos. Ya que, por ejemplo al utilizarlos podemos:

• Reducir el trafico de red en los servidores.
• Y, mejora nuestra seguridad.

Existen diferentes tipos de Stored Procedure, los cuales revisaremos más adelante y haremos algunos ejemplos, además, veremos algunas ventajas al utilizar un SP.

¿Qué es un Stored Procedure en SQL Server?

Un Stored Procedure o procedimiento almacenado lo podemos definir como un grupo de instrucciones almacenados físicamente en una base de datos.

Según la definición un procedimiento almacenado es muy parecido a ciertas estructuras de otros lenguajes de programación, debido a las siguientes características.

Características principales de un Stored Procedure en SQL Server

Podemos mencionar tres, principalmente:

• Un SP puede o no, aceptar parámetros de entrada y devolver uno o múltiples parámetros de salida.
• Internamente un SP cuenta con sentencias o clausulas que llevan acabo operaciones en la base de datos, ya sea: eliminar, seleccionar o insertar datos; incluso el llamado de otros Stored Procedure.
• Un SP es capaz de regresar el estado de ejecución, ya sea para notificar, éxito o fracaso, en caso de fracaso se puede regresar el motivo del error.

¿Por qué usar procedimiento almacenado?

Existen muchas ventajas y razones para utilizar Stored Procedures, por ejemplo: ocultar consultas SQL, por seguridad o reutilizar código, pero para que lo comprendas mejor, veamos las ventajas y beneficios de utilizar procedimientos almacenados.

Ventajas y beneficios de utilizar Stored Procedure en SQL Server

Continuando con la teoría detrás de los procedimientos almacenados, revisemos las ventajas y beneficios que tenemos al utilizar este tipo de elementos:

Reducción del trafico de red entre el cliente y servidor

• Todos los comandos dentro de un Stored Procedure son ejecutados en un solo lote o batch de código.
• Esto reduce el trafico de red entre el cliente y el servidor, ya que, solo se envía la instrucción de ejecución del SP.

Mayor seguridad

• Un Stored Procedure simplifica y en algunas veces elimina el requisito de otorgar ciertos permisos a nivel individual a los usuarios.
• Un SP controla los procesos y acciones que se realizan dentro de el, además, protege los objetos de la base de datos en los que lleva acabo sus operaciones.
• Los puntos anteriores indica que a través de un SP diferentes usuarios o programas, pueden llevar acabo operaciones en objetos subyacentes de la base de datos, en los cuales, pueden o no tener permisos otorgados directamente en dichos objetos.
• A través de un Stored Procedure podemos extender los permisos de un usuario que solo tiene permisos de ejecución, por ejemplo:
  • A través de EXECUTE AS podemos habilitar algo llamado suplantación de usuario.
  • Con ello, el usuario que ejecuta el SP, puede llevar a acabo los TRUNCATE TABLE que se encuentren dentro del SP, sin tener los permisos directamente para esta clausula.
  • ¿Por qué tiene ventajas esto? Pues bien, para ejecutar un TRUNCATE TABLE, el usuario debe tener habilitado los permisos de ALTER TABLE, y un  usuario mal intencionado, podría ejecutar no solo un TRUNCATE TABLE, sino modificar la estructura de la tabla.
• Otro aspecto que mitigan los Stored Procedure. es que, al ejecutar un SP, los usuarios conectados a la red, sólo verán la instrucción de la llamada del SP y, todos los otros elementos como: nombres de tablas, campos, etc.; quedan ocultos a todos los usuarios de la red, por lo que los usuarios mal intencionados no podrán conocer a ciencia cierta nuestra base de datos.
• Los SP que cuentan con parámetros son menos susceptibles a ataques de inyección SQL, ya que, los parámetros de entrada son tratados como un valor literal y no como un código ejecutable.
• Otro aspecto relevante es que los Stored Procedure pueden ser cifrados, lo que permite ofuscar el código fuente.

Reutilización de código

• Si tenemos procesos repetitivos, un Stored Procedure puede encapsular estos procesos y ahorrarnos el volver a escribir las mismas instrucciones.
• Con esto, cualquier usuario o aplicación podría ejecutar dicho proceso dentro del SP.

Facilita el mantenimiento

• Si mantenemos las operaciones en el nivel de datos, solo los procedimientos almacenados deberán ser actualizados debido a cambios de diseño en nuestras tablas o campos, a menos, que los parámetros de entrada y salida cambien.
• Esta separación aíslan los aplicativos y usuarios conectados a la base de datos, ya que, no es necesario que se enteren de todos los cambios de diseño, ni las relaciones y procesos de nuestra base de datos.

Mejor rendimiento

• La primera vez que un Stored Procedure se ejecuta, este es compilado y se crea un plan de ejecución que es reutilizado en futuras ejecuciones.
• Dado que el procesador de consultas no debe crear un nuevo plan cada vez que ejecuta el SP, se tiene un menor tiempo de procesamiento por SP.
• El compilado de un SP es algo un poco más avanzado, no tocaremos este tema en este post, pero, ten en mente que, si hay cambios significativos en los objetos de la base de datos, el compilado anterior podría ralentizar la ejecución del SP, así que pensar recompilar y crear un nuevo plan, no es mala idea.

Tipos de Stored Procedure en SQL Server

Excelente, ya conoces las ventajas y beneficios de los Stored Procedure, ahora analicemos los diferentes tipos que existen:

Stored Procedure definidos por el usuario (User-defined)

• Son aquellos definidos por usuarios, ya sea, en una base de datos creada por los mismos usuarios o en todas las bases de datos del sistema con excepción a la base de datos de recursos (Resource database).

Stored Procedure temporal

• Los procedimientos almacenados temporales, también son definidos por los usuarios.
• Son similares a los SP permanentes, sin embargo, los SP temporales son almacenados en la base de datos tempdb.
• Existen a su vez dos tipos de Stored Procedures temporales:
  • Locales: similar a las tablas temporales, su nombre inicia con un solo “#” y únicamente son visibles en la conexión del usuario actual, es eliminado cuando se cierra la conexión actual.
  • Globales: su nombre inicia con dos “##”, son visibles para todos los usuarios después de su creación y es eliminado después de la ultima conexión que lo utilice.

Stored Procedure de sistema

• Este tipo de SP vienen por default en SQL Server.
• Son almacenados en la base de datos de recursos y son visibles desde cada base de datos definidas por el usuario.
• La base de datos msdb (La base de datos msdb es usada por SQL Server para guardar varios historiales) también cuenta con este tipo de SP en el esquema dbo que se utilizan para programar alertas y diversos trabajos.
• Por lo regular, el nombre de este tipo de SP inicia con el prefijo “sp_”, por lo que no se recomienda utilizar este prefijo en los SP creados por los usuarios.

Stored Procedure extendidos creados por los usuarios

• Son aquellos Stored Procedure que permiten crear rutinas externas en lenguajes de programación como C.
• Dichos SP son contenidos en archivos DLL (Biblioteca que contiene código y datos que puede usar más de un programa al mismo tiempo) donde una instancia de SQL Server puede leer y ejecutar.

Excelente ¿Qué te ha parecido este post hasta el momento? Espero que te este gustando, para complementar, pasemos hacer algunos ejemplos para que afiances todo lo aprendido.

Sintaxis general T-SQL de un Stored Procedure

Te dejo la siguiente sintaxis en T-SQL que utilizaremos en todos los ejemplos siguientes, ya que, para crear o modificar un SP, la sintaxis es prácticamente igual.

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

De esta sintaxis podemos destacar:

• CREATE: especifica la creación de un SP.
• ALTER: indica que modificaremos un SP existente.
• procedure_name: nombre del SP en cuestión.
• BEGIN: inicia la lógica que procesará nuestro SP.
• sql_statement: lógica que enjutará nuestro SP.
• END: finaliza nuestro SP.

Ahora si, veamos algunos ejemplos.

CREATE PROCEDURE: ¿Cómo crear un Stored Procedure en SQL Server y regresar datos (Return data)?

Bien, en este primer ejemplo te enseñare a crear un Stored Procedure en T-SQL a través de CREATE PROCEDURE, la creación de un SP desde mi punto de vista es algo sencillo, lo complicado es programar la lógica en su interior, lo cual, de pende en obvias razones, de la aplicación y algoritmo que estés desarrollando.

¿Qué es y para qué sirve CREATE PROCEDURE?

El comando CREATE PROCEDURE nos permite crear un Stored Procedure o procedimiento almacenado dentro de una base de datos, que en este caso es SQL Server.

Mi primer Stored Procedure: “Hola mundo”

Bien, ahora si veamos como crear el Stored Procedure más simple del mundo, ya que solo imprimirá la frase “Hola mundo”.

• Es recomendable en la practica agregar algún prefijo al nombre del SP, por ejemplo, "admin_" o “usuarios_”, etc.; en nuestros ejemplos no lo pondré, pero tu si puedes agregarlos si lo deseas.

USE ewebikdb;
GO
CREATE PROCEDURE hola_mundo
AS
BEGIN
	SELECT 'Hola mundo';
END
GO

Como puedes ver:

• Nuestro código inicia especificando la base de datos en la que deseamos trabajar.
• La instrucción GO indica la finalización de un lote de instrucciones, por ejemplo “USE ewebikdb”.
• Ahora, la clausula CREATE PROCEDURE debe ser la primera instrucción o clausula de nuestro lote o batch de nuestro query (consulta), es por ello que le antecede el GO, para indicar que a partir de CREATE PROCEDURE inicia un nuevo batch, de lo contrario SQL Server lanzaría el siguiente error: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
• hola_mundo: es el nombre de nuestro Stored Procedure.
• AS: es una clausula de seguridad, con la cual llevamos acabo la suplantación de usuario que te mencione en los beneficios de un SP, más adelante haremos un ejemplo.
• Entre las clausulas BEGIN y END escribimos la lógica de nuestro Stored Procedure.
• Nuestro Return data: es el mismo SELECT.
• Si ejecutamos nuestro query, crearemos nuestro SP de nombre “hola_mundo” y, podemos ver si se ha creado correctamente, si en SQL Server Management Studio expandimos nuestra base de datos y nos vamos al apartado Programmability y después Stored Procedures, podremos ver el SP que acabamos de crear. Te recomiendo que des clic derecho sobre Programmability y des clic en Refresh, en dado caso que no te aparece el SP.

• Nuestro SP se creo sin ningún problema.

EXEC Stored Procedure: ¿Cómo ejecutar o llamar un Stored Procedure?

Bien, ya hemos creado nuestro SP, ahora llamémoslo o ejecutémoslo para que lleve acabo la lógica que hemos programado entre el BEGIN Y END, sólo necesitamos escribir la clausula EXEC seguido del nombre del Stored Procedure.

USE ewebikdb;

EXEC hola_mundo;

• La ejecución del código anterior nos regresa el mensaje “Hola mundo” que es el SELECT que programamos en nuestro SP.

CREATE PROCEDURE: ¿Cómo crear un Stored Procedure con parámetros de entrada en SQL Server?

Para ejemplificar la creación de un Stored Procedure que recibe parámetros de entrada y los regresa a la salida, vamos a crear un nuevo SP de nombre “tunel”, el cual simplemente regresará el valor de los parámetros de entrada mediante un SELECT.

USE ewebikdb;
GO
CREATE PROCEDURE tunel
@mensaje1			VARCHAR(50),
@mensaje2			VARCHAR(50)
AS
BEGIN
	SELECT @mensaje1, @mensaje2;
END
GO

• La sintaxis de creación es la misma, solo que ahora, entre CREATE PROCEDURE y AS, agregamos la lista de los parámetros que recibirá nuestro SP:
• @mensaje1 es nuestro primer parámetro de entrada de tipo VARCHAR(50).
• @mensaje2 es nuestro segundo parámetro de entrada de tipo VARCHAR(50).
• Tu puedes agregar los parámetros que requieras y los tipos que necesites.

EXEC Stored Procedure: ¿Cómo ejecutar o llamar un Stored Procedure con parámetros de entrada?

Ahora ejecutemos nuestro Stored Procedure con parámetros mediante EXEC, seguido del nombre y parámetros de entrada, por ejemplo:

USE ewebikdb;

EXEC tunel 'Hola', 'mundo';

• El resultado es el siguiente:

ALTER PROCEDURE: ¿Cómo modificar un Stored Procedure en SQL Server?

Bien, ya tenemos creados un par de Stored Procedure creados, ahora vamos a aprender como podemos modificar un SP existente, por ejemplo, vamos a modificar el SP “tunel”:

• Agregaremos un nuevo parámetro de entrada.
• Agregaremos la lógica para detectar si un parámetro de entrada viene en blanco.
• Y agregaremos un valor por defecto a los parámetros de entrada, para que SQL Server no lance un error en dado caso de que algún parámetro no sea pasado en el EXEC.

El código quedaría de la siguiente manera:

USE ewebikdb;
GO
ALTER PROCEDURE tunel
@mensaje1			VARCHAR(50) = '',
@mensaje2			VARCHAR(50) = '',
@mensaje3			VARCHAR(50) = ''
AS
BEGIN
DECLARE @mensaje_1  VARCHAR(50);
DECLARE @mensaje_2  VARCHAR(50);
DECLARE @mensaje_3  VARCHAR(50);
	
	IF @mensaje1 = ''
	BEGIN
		SET @mensaje_1 = 'Mensaje 1: sin mensaje';
	END
	ELSE
	BEGIN
		SET @mensaje_1 = @mensaje1;
	END

	IF @mensaje2 = ''
	BEGIN
		SET @mensaje_2 = 'Mensaje 2: sin mensaje';
	END
	ELSE
	BEGIN
		SET @mensaje_2 = @mensaje2;
	END

	IF @mensaje3 = ''
	BEGIN
		SET @mensaje_3 = 'Mensaje 3: sin mensaje';
	END
	ELSE
	BEGIN
		SET @mensaje_3 = @mensaje3;
	END

	SELECT @mensaje_1 AS 'Mensaje 1', @mensaje_2 AS 'Mensaje 2', @mensaje_3 AS 'Mensaje 3';

END
GO

• Lo primero que hicimos es cambiar el CREATE PROCEDURE por ALTER PROCEDURE, con el cual, indicamos que deseamos modificar un Stored Procedure existente.
• También agregamos un nuevo parámetro de entrada: @mensaje3 de tipo VARCHAR(50).
• Si eres observador, ya te habrás dado cuenta que en cada parámetro de entrada he agregado: “=''”; con lo cual, le indico a SQL Server que si, un parámetro no llegará a ser especificado en la llamada del SP, pase un valor vacío por default, así nuestro SP, no lanzará un error por la falta de un parámetro de entrada.
• Por ultimo, para detectar si los mensajes vienen vacíos, utilice algunos IF-ELSE, con los cuales:
  • Primero declaro algunas variables dentro de nuestro SP que corresponden a cada uno de los parámetros de entrada.
  • Ahora, en el IF, evaluamos que si el mensaje viene vacío, entre al IF y establezca el mensaje en las variables declaradas que: Mensaje X: sin mensaje.
  • Por otro lado, si el mensaje no viene vacío, entre al ELSE y establezca en las variables el valor del parámetro de entrada correspondiente, por ejemplo para el @mensaje_3: SET @mensaje_3 = @mensaje3;
• Como puedes observar, el IF-ELSE, prácticamente funciona igual que en otros lenguajes de programación, sólo que en este caso, cada sección del IF o ELSE, te recomiendo que los envuelvas entre un BEGIN - END, para que tu código sea más fácil de leer.
• Ahora, con DECLARE, podemos crear variables en SQL Server y asignarles un tipo, incluso, podríamos asignarles un valor durante su declaración.
• En cuanto a SET, nos sirve para asignar un valor a una variable declarada, como es el caso de @mensaje_1, @mensaje_2 y @mensaje_3.

Y listo, ya hemos modificado nuestro primer SP, ahora enjutémoslo, con los siguientes tres casos de uso:

• Pasando los tres parámetros con valor diferente a vacío.
• Pasando un parámetro en vacío para validar nuestros IF - ELSE.
• Pasar únicamente 2 parámetros, SQL Server no debería mandar error y el SP debería ejecutar su lógica sin ningún problema.

USE ewebikdb;

EXEC tunel 'Hola', 'mundo', 'by EWebik';

EXEC tunel '', 'mundo', 'by EWebik';

EXEC tunel 'Hola', 'mundo';

• Y el resultado sería el siguiente, como ves, no mando ningún error.

sp_rename: ¿Cómo modificar el nombre de un SP?

Bien, ahora que ya tenemos un par de Stored Procedures creados, vamos a renombrar uno de ellos, para ello utilizaremos EXEC sp_rename, con el cual cambiamos el nombre de un SP existente, no obstante, debes tener en cuenta lo siguiente:

Restricciones de cambio de nombre de un Stored Procedure en SQL Server

Quizá estas restricciones suenen o requieran un conocimiento un poco más avanzado de SQL Server, pero las pongo para que te des una idea y conforme vayas avanzando, cuando repases este tema, lo comprendas mejor.

• El nombre del SP requiere que cumpla con las reglas de los identificadores.
• Al modificar el nombre de un Stored Procedure, se conservan los object_id y permisos asignados al SP.
• Object_ID: es un número de identificación único para cada objeto dentro de la base de datos de SQL Server.
• Al modificar el nombre de un Stored Procedure no cambia el nombre del objeto correspondiente en la columna de definición de la vista de catálogo sys.sql_modules.
• Si otros objetos de la base de datos son dependientes del SP al que le estas modificando el nombre, como pueden ser otros Stored Procedure, pueden lanzar errores en su ejecución.

Bien, ahora si, para cambiar el nombre solo requerimos la siguientes líneas de código:

USE ewebikdb;

EXEC sp_rename 'tunel', 'seguidor';

• Como puedes ver, sp_rename recibe como primer parámetro el nombre actual del Stores Procedure y como segundo parámetro el nuevo nombre.

DROP PROCEDURE: ¿Cómo eliminar un Stored Procedure en SQL Server?

Excelente, en este punto ya conoces los aspectos básicos de un Stored Procedure, como crearlos y modificarlos, ahora vas aprender como eliminar un SP que ya no deseas tener en tu base de datos, para ello, eliminaremos el SP “hola_mundo”.

Restricciones de eliminar un Stored Procedure en SQL Server

• Al eliminar un SP y no actualizar las dependencias a este SP, los objetos que hacen referencia al SP eliminado lanzaran errores.
• Si se vuelve a crear un SP con el mismo nombre que el eliminado, los objetos con dependencias al SP eliminado, funcionarán, pero, lo harán con la lógica que tenga el nuevo SP, así que, quizá no tengas errores de que no exista el SP, pero podrías tener errores en cuanto a la nueva lógica interna del nuevo Stored Procedure.

Ahora si, procedamos a eliminar nuestro Stored Procedure a través de T-SQL:

USE ewebikdb;

DROP PROCEDURE hola_mundo;

• Y listo, al ejecutar el código anterior eliminarás el SP “hola_mundo”.

Conclusión: Stored Procedure en SQL Server

Hemos llegado al final de este post, hoy has aprendido:

• Cómo crear, modificar y eliminar un Stored Procedure.
• Pasar parámetros de entrada a un Stored Procedure.
• Regresar datos en un Stored Procedure.
• Ejecutar o llamar un Stored Procedure.

Ahora, como pudiste ver la lógica interna de cada SP ha sido muy sencilla, y esto se debe a que cada SP es distinto y los SP que llegues a crear, tendrán una lógica propia dependiente de tu aplicación y no tendrá nada que ver con los que creamos hoy.

Bien, recuerda que si tienes dudas, puedes mandarme un correo o puedes ver el vídeo de la clase, si aun no esta el vídeo, por favor, suscríbete a mi boletín y al canal de YouTube para que te avise cuando suba los vídeos correspondientes, nos vemos en la próxima clase.