Los procedimientos almacenados en SQL Server son herramientas fundamentales 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 qué son, sus tipos, cómo crearlos, eliminarlos, modificarlos y cómo trabajar con sus potentes parámetros de entrada y salida, con ejemplos claros y prácticos.
¿Qué son los Procedimientos Almacenados en SQL Server? Fundamentos Esenciales
Un procedimiento almacenado es un conjunto de instrucciones SQL (y otras sentencias de control de flujo) 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 o complejas, permitiendo que se ejecuten con una única llamada, lo que simplifica la administración y el desarrollo.
SQL Server soporta varios tipos de procedimientos almacenados:
- Del sistema: Son procedimientos predefinidos por SQL Server, almacenados en la base de datos "master". Llevan el prefijo "sp_" (ej.
sp_help). Permiten recuperar información de las tablas del sistema y pueden ejecutarse en cualquier base de datos. - Locales: Son los creados por los usuarios para tareas específicas de una base de datos.
- Temporales: Se dividen en locales (nombres con #, disponibles solo en la sesión del usuario) y globales (nombres con ##, disponibles en todas las sesiones).
- Extendidos: Implementados como bibliotecas de vínculos dinámicos (DLL), se ejecutan fuera del entorno de SQL Server y suelen llevar el prefijo "xp_".
Cuando creas un procedimiento almacenado, SQL Server analiza la sintaxis de las instrucciones. Si no hay errores, guarda el nombre en sys.objects y el contenido en sys.sql_modules. Es importante recordar que un procedimiento almacenado puede referenciar objetos que no existen en el momento de su creación, pero deben existir cuando se ejecute.
Ventajas de usar Procedimientos Almacenados en SQL Server
La implementación de procedimientos almacenados ofrece beneficios significativos que impactan directamente en la eficiencia y robustez de tus aplicaciones y bases de datos. Conocer estas ventajas es clave para optimizar tu trabajo.
- Comparten la lógica de la aplicación: Centralizan el acceso y las modificaciones de los datos en un solo lugar, lo que facilita el mantenimiento y la consistencia.
- Mejoran la seguridad: Permiten a los usuarios realizar operaciones sin tener acceso directo a las tablas subyacentes, controlando qué acciones pueden ejecutar.
- Reducen el tráfico de red: En lugar de enviar múltiples instrucciones SQL desde el cliente al servidor, se envía una única instrucción para ejecutar el procedimiento, disminuyendo el número de solicitudes y la carga de la red.
Además, los procedimientos almacenados se crean en la base de datos seleccionada (a excepción de los temporales, que van a tempdb). Pueden hacer referencia a tablas, vistas, funciones, otros procedimientos y tablas temporales. Pueden incluir cualquier tipo y cantidad de instrucciones, salvo algunas excepciones como create default, create procedure, create rule, create trigger y create view.
Si un procedimiento almacenado crea una tabla o variable temporal, estas solo existen dentro de su ámbito y desaparecen al finalizar la ejecución.
Creación de Procedimientos Almacenados en SQL Server: Sintaxis y Ejemplos
Crear un procedimiento almacenado es un proceso sencillo que implica definir su nombre y las instrucciones que contendrá. Antes de crear un procedimiento, es una buena práctica tipear y probar las instrucciones SQL por separado para asegurar que obtienen el resultado esperado.
La sintaxis básica para crear un procedimiento almacenado es:
create procedure NOMBREPROCEDIMIENTO as INSTRUCCIONES;
-- o de forma abreviada
create proc NOMBREPROCEDIMIENTO as INSTRUCCIONES;
Ejemplo 1: Creación de una tabla y inserción de datos
Este ejemplo ilustra un procedimiento que crea una tabla libros y luego inserta varios registros. Es útil para configurar un entorno de prueba rápidamente.
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);
Para ejecutar este procedimiento, simplemente usamos:
exec pa_crear_libros;
Después de ejecutarlo, puedes verificar la existencia de la tabla y sus datos. También puedes usar exec sp_help pa_crear_libros para ver la información del procedimiento.
Ejemplo 2: Consulta de libros con bajo stock
Este procedimiento muestra los libros con una cantidad igual o superior a 10. Es un ejemplo práctico para gestionar inventarios.
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;
Para ejecutarlo:
exec pa_libros_limite_stock;
Si actualizamos una cantidad a un valor bajo y volvemos a ejecutar el procedimiento, veremos cómo el resultado cambia, demostrando la reactividad del procedimiento a los datos actuales.
Cómo eliminar un Procedimiento Almacenado en SQL Server de forma segura
La eliminación de procedimientos almacenados es una tarea común. Es crucial hacerlo de manera segura para evitar errores si el procedimiento no existe. Puedes emplear las instrucciones drop procedure o drop proc.
La sintaxis básica es:
drop procedure NOMBREPROCEDIMIENTO;
-- o
drop proc NOMBREPROCEDIMIENTO;
Para evitar errores si el procedimiento que quieres eliminar no existe, puedes usar una verificación previa:
if object_id('NOMBREPROCEDIMIENTO') is not null
drop procedure NOMBREPROCEDIMIENTO;
O, si prefieres un mensaje informativo:
if object_id('NOMBREPROCEDIMIENTO') is not null
drop procedure NOMBREPROCEDIMIENTO
else
sélect 'No existe el procedimiento NOMBREPROCEDIMIENTO';
Parámetros en Procedimientos Almacenados: Entrada y Salida
Los parámetros son esenciales para hacer que los procedimientos almacenados sean flexibles y reutilizables, permitiéndoles recibir y devolver información. Se definen después del nombre del procedimiento, comienzan con @ (arroba) y son locales al procedimiento.
Parámetros de Entrada: Pasando información a un procedimiento
Los parámetros de entrada permiten pasar valores al procedimiento. Se declaran con un nombre y un tipo de dato, y opcionalmente, un valor por defecto.
Sintaxis:
create proc NOMBREPROCEDIMIENTO
@NOMBREPARAMETRO TIPO
as SENTENCIAS;
-- Con valor por defecto:
create proc NOMBREPROCEDIMIENTO
@NOMBREPARAMETRO TIPO=VALORPORDEFECTO
as SENTENCIAS;
Los valores pueden pasarse por posición (en el mismo orden de declaración) o por nombre.
Ejemplo 1: Búsqueda de libros por autor
create procedure pa_libros_autor @autor varchar(30)
as
select titulo, editorial, precio
from libros
where autor = @autor;
Para ejecutar y buscar libros de 'Borges':
exec pa_libros_autor 'Borges';
Ejemplo 2: Búsqueda por autor y editorial (pasando parámetros por nombre)
create procedure pa_libros_autor_editorial
@autor varchar(30),
@editorial varchar(20)
as
select titulo, precio
from libros
where (autor = @autor) and (editorial=@editorial);
Para ejecutar usando nombres de parámetros, sin importar el orden:
exec pa_libros_autor_editorial @editorial='Planeta', @autor='Richard Bach';
Ejemplo 3: Parámetros con valores por defecto
Si intentamos ejecutar el procedimiento anterior sin valores para los parámetros, obtendríamos un error. Para permitir omitir valores, debemos definir valores por defecto al crear el procedimiento.
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, podemos ejecutarlo sin parámetros, y utilizará los valores por defecto:
exec pa_libros_autor_editorial_2;
Parámetros de Salida (OUTPUT): Devolviendo información
Los parámetros de salida permiten que un procedimiento almacenado devuelva información al proceso que 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; -- Asigna el valor al parámetro de salida
Ejemplo 1: Calcular el 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 ejecutarlo, debes declarar una variable local para recibir el valor de salida y usar la palabra clave OUTPUT:
declare @resultado decimal(4,2);
exec pa_promedio 5.5, 6.5, @resultado output;
select @resultado as promedio;
Ejemplo 2: Suma y promedio de precios de libros por autor
Este ejemplo más complejo busca libros por autor y devuelve la suma total y el promedio de sus precios.
-- Creamos un procedimiento almacenado que muestre los títulos, editorial y precio
-- de los libros de un determinado autor (enviado como parámetro de entrada)
-- y nos retorne la suma y el promedio de los precios de todos los libros del autor enviado:
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 para 'Richard Bach':
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;
Incluso puedes ejecutarlo omitiendo el parámetro de entrada si tiene un valor por defecto, especificando solo los parámetros de salida por su nombre:
declare @s3 decimal(6,2), @p3 decimal(6,2);
exec pa_autor_sumaypromedio @suma=@s3 output, @promedio=@p3 output;
select @s3 as total, @p3 as promedio;
Administración e Información de Procedimientos Almacenados en SQL Server
SQL Server proporciona varias herramientas y procedimientos del sistema para obtener información sobre los procedimientos almacenados, sus dependencias y su contenido.
exec sp_help;: Muestra una lista de todos los objetos en la base de datos actual, incluyendo tablas y procedimientos almacenados.exec sp_help NOMBRE_PROCEDIMIENTO;: Ofrece detalles específicos sobre un procedimiento, incluyendo sus parámetros (Parameter_name,Type,Length, etc.) y fecha de creación (Created_datetime).exec sp_helptext NOMBRE_PROCEDIMIENTO;: Muestra el texto SQL de un procedimiento almacenado. Esto es útil para revisar su lógica.exec sp_stored_procedures;: Lista todos los procedimientos almacenados en la base de datos actual, incluyendo metadatos como el número de parámetros de entrada/salida.exec sp_stored_procedures @sp_name='pa_%';: Permite filtrar procedimientos por nombre, por ejemplo, mostrando todos los que empiezan con "pa_".exec sp_depends NOMBRE_OBJETO;: Muestra los objetos de los que depende un procedimiento almacenado (ej. tablas) o los objetos que dependen de una tabla (ej. procedimientos).
Encriptación de Procedimientos Almacenados: Protegiendo tu Código
Si deseas ocultar el contenido de un procedimiento almacenado para proteger tu lógica de negocio, puedes encriptarlo usando la opción WITH ENCRYPTION al crearlo. Esto impide que los usuarios (o sp_helptext) puedan leer su código.
Sintaxis:
create proc NOMBREPROCEDIMIENTO PARAMETROS
with encryption
as INSTRUCCIONES;
Ejemplo: Encriptando pa_autor_sumaypromedio
if object_id('pa_autor_sumaypromedio') is not null
drop procedure pa_autor_sumaypromedio;
go
create procedure pa_autor_sumaypromedio
@autor varchar(30)='%',
@suma decimal(6,2) output,
@promedio decimal(6,2) output
with encryption
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
Al intentar ver el texto con exec sp_helptext pa_autor_sumaypromedio;, recibirás un mensaje indicando que el texto está encriptado.
Modificación de Procedimientos Almacenados con ALTER PROCEDURE
Con el tiempo, es posible que necesites modificar un procedimiento almacenado debido a cambios en la lógica de negocio o la estructura de las tablas. Para esto, se utiliza la instrucción ALTER PROCEDURE.
Sintaxis:
alter proc NOMBREPROCEDIMIENTO PARAMETROS
as SENTENCIAS;
Ejemplo: Modificando un procedimiento encriptado
Podemos modificar el procedimiento pa_autor_sumaypromedio para que muestre el autor y, al mismo tiempo, quitarle la encriptación. Es importante notar que al usar ALTER PROCEDURE sin WITH ENCRYPTION, la encriptación se elimina automáticamente.
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 la modificación, exec sp_helptext pa_autor_sumaypromedio; mostrará el código SQL sin encriptar, y la ejecución del procedimiento reflejará los cambios.
Preguntas Frecuentes sobre Procedimientos Almacenados en SQL Server
Aquí respondemos a algunas de las dudas más comunes que surgen al trabajar con procedimientos almacenados.
¿Cuál es la diferencia entre un procedimiento almacenado y una función en SQL Server?
Aunque ambos son bloques de código precompilados, un procedimiento almacenado puede realizar operaciones DDL (crear/modificar objetos) y DML (insertar, actualizar, eliminar datos), no tiene que devolver un valor y puede tener parámetros de salida. Una función, en cambio, debe devolver un valor, no puede realizar DDL/DML que modifique el estado de la base de datos (solo lectura) y puede usarse directamente dentro de sentencias SQL como SELECT o WHERE.
¿Por qué se dice que los procedimientos almacenados mejoran el rendimiento?
Los procedimientos almacenados se compilan y almacenan en un formato ejecutable la primera vez que se usan, o incluso antes (precompilación). Esto significa que las ejecuciones posteriores son más rápidas ya que SQL Server no necesita volver a analizar y optimizar el plan de ejecución de las sentencias SQL cada vez. Además, al enviar menos datos a través de la red (una única llamada al procedimiento en lugar de múltiples sentencias), se reduce el tráfico y la latencia.
¿Puedo llamar un procedimiento almacenado desde otro procedimiento almacenado?
Sí, es posible y una práctica común. Un procedimiento almacenado puede contener llamadas a otros procedimientos almacenados, lo que permite modularizar aún más la lógica y crear flujos de trabajo complejos y reutilizables. Sin embargo, debes tener cuidado con la recursividad infinita si un procedimiento se llama a sí mismo sin una condición de terminación.
¿Qué ocurre si un procedimiento almacenado referencia una tabla que luego es eliminada?
Cuando se crea un procedimiento almacenado, no se verifica si todos los objetos referenciados existen. Sin embargo, si un procedimiento almacenado intenta ejecutar una operación en un objeto (como una tabla) que ha sido eliminada después de la creación del procedimiento, la ejecución fallará con un error indicando que el objeto no existe (por ejemplo, "Invalid object name 'nombre_tabla'"). Los objetos deben existir en el momento de la ejecución del procedimiento.