Procedimientos Almacenados en SQL Server

Los procedimientos almacenados en SQL Server son una herramienta fundamental para cualquier desarrollador o administrador de bases de datos. Permiten encapsular lógica de negocio, mejorar el rendimiento y aumentar la seguridad. En esta guía completa, exploraremos desde su definición hasta su gestión avanzada, con ejemplos prácticos para estudiantes.

¿Qué Son los Procedimientos Almacenados en SQL Server? (Definición y Tipos)

Un procedimiento almacenado es un conjunto de instrucciones SQL a las que se les asigna un nombre y se almacenan en el servidor de la base de datos. Su propósito principal es encapsular tareas repetitivas, haciendo más eficiente el acceso y modificación de datos.

Tipos de Procedimientos Almacenados en SQL Server

SQL Server clasifica los procedimientos almacenados en varios tipos:

• Del sistema: Almacenados en la base de datos "master" y con prefijo "sp_". Permiten recuperar información de las tablas del sistema y pueden ejecutarse en cualquier base de datos.
• Locales: Son los creados por el usuario, siendo el enfoque principal de esta guía.
• Temporales: Existen en la base de datos "tempdb". Pueden ser:
• Locales: Nombres que comienzan con un solo símbolo # (ej. #pa_temporal). Disponibles solo en la sesión del usuario que los crea y se eliminan al finalizar la sesión.
• Globales: Nombres que comienzan con dos símbolos ## (ej. ##pa_global). Disponibles en las sesiones de todos los usuarios.
• Extendidos: Implementados como bibliotecas de vínculos dinámicos (DLL) y se ejecutan fuera del entorno de SQL Server, generalmente con el prefijo "xp_".

Ventajas de Usar Procedimientos Almacenados

El uso de procedimientos almacenados ofrece múltiples beneficios:

• Comparten la lógica de la aplicación: Centralizan el acceso y las modificaciones de datos en un solo lugar, facilitando el mantenimiento.
• Evitan acceso directo a tablas: Los usuarios pueden interactuar con los datos a través de los procedimientos, sin necesidad de permisos directos sobre las tablas.
• Reducen el tráfico de red: En lugar de enviar múltiples instrucciones, los clientes ejecutan operaciones enviando una única instrucción al servidor.

Creación de un Procedimiento Almacenado en SQL Server (Sintaxis y Ejemplos)

Para crear un procedimiento almacenado, se utiliza la instrucción CREATE PROCEDURE o su forma abreviada CREATE PROC. Es importante probar las instrucciones SQL antes de encapsularlas en un procedimiento.

La sintaxis básica es:

CREATE PROCEDURE NOMBREPROCEDIMIENTO AS INSTRUCCIONES;
-- o
CREATE PROC NOMBREPROCEDIMIENTO AS INSTRUCCIONES;

Ejemplo: Creación de una Tabla y Datos Iniciales

Consideremos un procedimiento que crea una tabla libros y la llena con datos. Primero, se recomienda verificar si el procedimiento ya existe para evitar errores.

IF OBJECT_ID('pa_crear_libros') IS NOT NULL
DROP PROCEDURE pa_crear_libros;
GO

CREATE PROC pa_crear_libros AS
IF OBJECT_ID('libros') IS NOT NULL
DROP TABLE libros;

CREATE TABLE libros(
codigo INT IDENTITY,
titulo VARCHAR(40),
autor VARCHAR(30),
editorial VARCHAR(20),
precio DECIMAL(5,2),
cantidad SMALLINT,
PRIMARY KEY(codigo)
);

INSERT INTO libros (titulo, autor, editorial, precio, cantidad)
VALUES
('Uno', 'Richard Bach', 'Planeta', 15,5),
('Ilusiones', 'Richard Bach', 'Planeta', 18,50),
('El aleph', 'Borges', 'Emece', 25,9),
('Aprenda PHP', 'Mario Molina', 'Nuevo siglo', 45,100),
('Matematica estas ahi', 'Paenza', 'Nuevo siglo', 12,50),
('Java en 10 minutos', 'Mario Molina', 'Paidos', 35,300);
GO

Para ejecutar este procedimiento, simplemente usamos EXEC pa_crear_libros;.

Ejemplo: Consulta de Libros con Bajo Stock

Ahora, crearemos un procedimiento para mostrar libros con una cantidad específica en stock:

IF OBJECT_ID('pa_libros_limite_stock') IS NOT NULL
DROP PROCEDURE pa_libros_limite_stock;
GO

CREATE PROC pa_libros_limite_stock AS
SELECT * FROM libros WHERE cantidad >= 10;
GO

Al ejecutar EXEC pa_libros_limite_stock;, veremos los libros que cumplen esa condición.

Eliminación de Procedimientos Almacenados en SQL Server

Para eliminar un procedimiento almacenado que ya no es necesario, se utiliza la instrucción DROP PROCEDURE o DROP PROC.

La sintaxis básica es:

DROP PROCEDURE NOMBREPROCEDIMIENTO;
-- o
DROP PROC NOMBREPROCEDIMIENTO;

Variantes para una Eliminación Segura

Para evitar errores si el procedimiento no existe, se puede usar una verificación:

IF OBJECT_ID('NOMBREPROCEDIMIENTO') IS NOT NULL
DROP PROCEDURE NOMBREPROCEDIMIENTO;

O incluso, mostrar un mensaje si no existe:

IF OBJECT_ID('NOMBREPROCEDIMIENTO') IS NOT NULL
DROP PROCEDURE NOMBREPROCEDIMIENTO
ELSE
SELECT 'No existe el procedimiento NOMBREPROCEDIMIENTO';

Parámetros en Procedimientos Almacenados de SQL Server

Los procedimientos almacenados pueden recibir y devolver información utilizando parámetros. Estos son variables locales al procedimiento, definidos después del nombre y precedidos por '@'. Pueden ser de cualquier tipo de dato (excepto cursor).

Parámetros de Entrada en SQL Server

Permiten pasar información al procedimiento. Se declaran al crear el procedimiento. La sintaxis es:

CREATE PROC NOMBREPROCEDIMIENTO @NOMBREPARAMETRO TIPO AS SENTENCIAS;
-- Con valor por defecto:
CREATE PROC NOMBREPROCEDIMIENTO @NOMBREPARAMETRO TIPO=VALORPORDEFECTO AS SENTENCIAS;

Ejemplo 1: Búsqueda por Autor (Paso de Parámetros Posicional)

CREATE PROCEDURE pa_libros_autor @autor VARCHAR(30)
AS
SELECT titulo, editorial, precio
FROM libros
WHERE autor = @autor;

Para ejecutar: EXEC pa_libros_autor 'Borges';

Ejemplo 2: Búsqueda por Autor y Editorial (Paso de Parámetros por Nombre)

Se pueden pasar valores por el nombre del parámetro, sin importar el orden:

CREATE PROCEDURE pa_libros_autor_editorial
@autor VARCHAR(30),
@editorial VARCHAR(20)
AS
SELECT titulo, precio
FROM libros
WHERE (autor = @autor) AND (editorial=@editorial);

Ejecuciones:

• EXEC pa_libros_autor_editorial 'Richard Bach', 'Planeta';
• EXEC pa_libros_autor_editorial @editorial='Planeta', @autor='Richard Bach';

Ejemplo 3: Parámetros con Valores por Defecto

Si no se proporcionan valores para los parámetros, la ejecución fallará a menos que se definan valores por defecto:

CREATE PROCEDURE pa_libros_autor_editorial_2
@autor VARCHAR(30)='Richard Bach',
@editorial VARCHAR(20)='Planeta'
AS
SELECT titulo, autor, editorial, precio
FROM libros
WHERE (autor = @autor) AND (editorial = @editorial);

Ahora, EXEC pa_libros_autor_editorial_2; se ejecutará usando los valores por defecto.

Parámetros de Salida en SQL Server

Permiten que un procedimiento devuelva información a quien lo llamó. Se declaran con la palabra clave OUTPUT. No pueden ser de tipo text, ntext o image.

Sintaxis:

CREATE PROC NOMBREPROCEDIMIENTO
@PARAMETRODEENTRADA TIPO=VALORPORDEFECTO,
@PARAMETRODESALIDA TIPO=VALORPORDEFECTO OUTPUT
AS
SENTENCIAS
SELECT @PARAMETRODESALIDA=SENTENCIAS;

Ejemplo 1: Calcular Promedio de Dos Números

IF OBJECT_ID('pa_promedio') IS NOT NULL
DROP PROC pa_promedio;
GO
CREATE PROCEDURE pa_promedio
@n1 DECIMAL(4,2),
@n2 DECIMAL(4,2),
@resultado DECIMAL(4,2) OUTPUT
AS
SELECT @resultado=(@n1+@n2)/2;
GO

Para usarlo, declaramos una variable en la sesión y la pasamos como OUTPUT:

DECLARE @promedio DECIMAL(4,2);
EXEC pa_promedio 9, 10, @promedio OUTPUT;
SELECT @promedio AS promedio;

Ejemplo 2: Suma y Promedio de Precios por Autor

Este procedimiento muestra los libros de un autor y devuelve la suma y el promedio de sus precios.

IF OBJECT_ID('pa_autor_sumaypromedio') IS NOT NULL
DROP PROC pa_autor_sumaypromedio;
GO
CREATE PROCEDURE pa_autor_sumaypromedio
@autor VARCHAR(30)='%',
@suma DECIMAL(6,2) OUTPUT,
@promedio DECIMAL(6,2) OUTPUT
AS
SELECT titulo,editorial,precio
FROM libros
WHERE autor LIKE @autor;

SELECT @suma=SUM(precio)
FROM libros
WHERE autor LIKE @autor;

SELECT @promedio=AVG(precio)
FROM libros
WHERE autor LIKE @autor;
GO

Ejecución:

DECLARE @s DECIMAL(6,2), @p DECIMAL(6,2);
EXEC pa_autor_sumaypromedio 'Richard Bach', @s OUTPUT, @p OUTPUT;
SELECT @s AS total, @p AS promedio;

Encriptación de Procedimientos Almacenados en SQL Server

Para ocultar el contenido de un procedimiento almacenado y proteger su lógica, se puede encriptar utilizando la opción WITH ENCRYPTION al crearlo. Esto impide que los usuarios puedan leer su código.

Sintaxis:

CREATE PROC NOMBREPROCEDIMIENTO PARAMETROS
WITH ENCRYPTION
AS INSTRUCCIONES;

Si intentas ver el texto de un procedimiento encriptado con EXEC sp_helptext NOMBREPROCEDIMIENTO;, SQL Server responderá que "The text for object 'NOMBREPROCEDIMIENTO' is encrypted."

Modificación de Procedimientos Almacenados en SQL Server (ALTER PROCEDURE)

Los procedimientos almacenados pueden necesitar ser modificados debido a cambios en los requisitos de negocio o en la estructura de las tablas. Para ello, se utiliza la instrucción ALTER PROCEDURE.

Sintaxis:

ALTER PROC NOMBREPROCEDIMIENTO PARAMETROS
AS SENTENCIAS;

Ejemplo: Modificar y Desencriptar un Procedimiento

Podemos modificar el pa_autor_sumaypromedio para que muestre el autor y le quitamos la encriptación:

ALTER PROC pa_autor_sumaypromedio
@autor VARCHAR(30)='%',
@suma DECIMAL(6,2) OUTPUT,
@promedio DECIMAL(6,2) OUTPUT
AS
SELECT titulo,editorial,precio,autor
FROM libros
WHERE autor LIKE @autor;

SELECT @suma=SUM(precio)
FROM libros
WHERE autor LIKE @autor;

SELECT @promedio=AVG(precio)
FROM libros
WHERE autor LIKE @autor;
GO

Después de esto, EXEC sp_helptext pa_autor_sumaypromedio; mostrará el código modificado y desencriptado.

Información y Gestión de Procedimientos Almacenados en SQL Server

SQL Server ofrece diversas vistas y procedimientos de sistema para consultar información sobre los procedimientos almacenados y otros objetos de la base de datos.

• EXEC sp_help;: Muestra una lista de todos los objetos en la base de datos actual, incluyendo tablas y procedimientos almacenados.
• EXEC sp_help NOMBREPROCEDIMIENTO;: Proporciona detalles específicos sobre un procedimiento almacenado, incluyendo su nombre, propietario, tipo, fecha de creación y parámetros (si los tiene).
• EXEC sp_helptext NOMBREPROCEDIMIENTO;: Permite ver el código SQL de un procedimiento almacenado (si no está encriptado).
• EXEC sp_stored_procedures;: Lista todos los procedimientos almacenados en el servidor.
• EXEC sp_stored_procedures @sp_name='pa_%';: Permite filtrar procedimientos por un patrón en su nombre.
• EXEC sp_depends NOMBREPROCEDIMIENTO;: Muestra las dependencias de un procedimiento, es decir, qué tablas o vistas utiliza.
• EXEC sp_depends NOMBRETABLA;: Muestra qué procedimientos almacenados dependen de una tabla específica.

Preguntas Frecuentes sobre Procedimientos Almacenados en SQL Server

¿Cuál es la diferencia entre un procedimiento almacenado local y uno temporal en SQL Server?

Un procedimiento almacenado local es creado por el usuario y se almacena permanentemente en la base de datos seleccionada. Un procedimiento temporal (#nombre o ##nombre) se crea en la base de datos tempdb y su existencia está limitada: los locales temporales (#) solo duran la sesión del usuario, mientras que los globales temporales (##) están disponibles para todas las sesiones hasta que el servidor se reinicia o se eliminan explícitamente.

¿Por qué se recomienda utilizar IF OBJECT_ID() IS NOT NULL DROP PROCEDURE antes de crear un procedimiento almacenado?

Esta práctica es crucial para la robustez de los scripts. Si intentas crear un procedimiento con un nombre que ya existe, SQL Server generará un error. Al verificar primero con OBJECT_ID() y eliminarlo si existe, aseguras que el script se ejecute sin interrupciones y que siempre se cree la versión deseada del procedimiento.

¿Puedo utilizar cualquier tipo de dato para los parámetros de salida en un procedimiento almacenado?

No, los parámetros de salida pueden ser de casi cualquier tipo de dato, pero hay excepciones. No se permiten los tipos de datos text, ntext, ni image para los parámetros de salida. Es importante tener esto en cuenta al diseñar tus procedimientos que devuelven valores complejos.

¿Cómo puedo saber qué procedimientos almacenados utilizan una tabla específica en SQL Server?

Puedes utilizar el procedimiento del sistema EXEC sp_depends NOMBRETABLA;. Al pasarle el nombre de la tabla como argumento, te listará todos los objetos, incluyendo procedimientos almacenados, que tienen una dependencia directa con esa tabla, lo cual es útil para la gestión y el mantenimiento de la base de datos.

¿Qué sucede si un procedimiento almacenado hace referencia a un objeto que no existe al momento de su creación?

SQL Server analiza la sintaxis del procedimiento al crearlo, pero no verifica la existencia de todos los objetos referenciados. Esto significa que un procedimiento puede crearse exitosamente aunque haga referencia a una tabla o vista inexistente. Sin embargo, el procedimiento fallará con un error de objeto no encontrado al momento de su ejecución si dichos objetos no existen en ese momento. Los objetos deben existir cuando el procedimiento se invoca.