SQL Server

Procedimientos almacenados en SQL Server: introducción para principiantes

Aprende a crear, ejecutar, parametrizar y modificar un procedimiento almacenado sencillo de SQL Server y descubre en qué se diferencia de una vista.

Por JaviPublicado 12 min de lecturaPrincipiante

Un procedimiento almacenado es un objeto con nombre de la base de datos que contiene una o más instrucciones SQL. Una aplicación o una persona puede ejecutarlo por su nombre y proporcionar valores de entrada en vez de construir una consulta nueva cada vez. Se suele usar para ofrecer una operación coherente, mantener SQL repetido en un lugar y controlar qué operaciones puede hacer una cuenta de aplicación.

En esta lección crearemos dbo.usp_GetOrdersByCustomer, un procedimiento que devuelve los pedidos de un cliente en la base sintética JaviBuildsLab. Se apoya en SELECT, WHERE y en la idea de consulta reutilizable presentada con las vistas. Un procedimiento no es automáticamente más rápido, seguro o mejor que una consulta bien diseñada; su utilidad depende del uso y del mantenimiento.

Crear un procedimiento almacenado sencillo

En SSMS, conéctate a la base prevista y crea este procedimiento:

Consulta SQL: Crear un procedimiento almacenado sencilloT-SQL
CREATE PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, OrderDate, Status, Amount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC, OrderId DESC;
END;
T-SQL11 líneasVista de solo lectura. No está conectada a ninguna base de datos.

El esquema dbo forma parte del nombre. Algunas organizaciones usan el prefijo usp_ para reconocer procedimientos propios, pero SQL Server no lo exige. @CustomerId int declara un parámetro de entrada llamado @CustomerId cuyo valor debe ser un entero. La consulta usa ese valor como filtro. SET NOCOUNT ON evita el mensaje adicional de filas afectadas para las instrucciones dentro del procedimiento y puede facilitar el tratamiento del resultado por parte del cliente.

El cuerpo está entre BEGIN y END. Este ejemplo tiene un SELECT, aunque un procedimiento puede contener varias instrucciones. Mantén tus primeros procedimientos enfocados para que sus entradas, resultado y acciones sean fáciles de entender. ORDER BY define el orden devuelto por este SELECT; no es una propiedad general de los datos del procedimiento.

En SSMS, ejecuta CREATE en la base JaviBuildsLab. CREATE PROCEDURE debe ser la primera instrucción de su lote. Si hay otras instrucciones antes, sepáralas con GO o crea el procedimiento en otra ventana. En el Explorador de objetos, expande Programmability, después Stored Procedures y actualiza el nodo para ver dbo.usp_GetOrdersByCustomer.

Ejecutar el procedimiento

Llama al procedimiento con EXEC (o la palabra completa EXECUTE) y proporciona el identificador de un cliente:

Consulta SQL: Ejecutar el procedimientoT-SQL
EXEC dbo.usp_GetOrdersByCustomer @CustomerId = 11;
T-SQL1 líneaVista de solo lectura. No está conectada a ninguna base de datos.

La forma con el nombre del parámetro deja claro qué valor se asigna. SQL Server ejecuta el SELECT usando 11 como @CustomerId y devuelve los pedidos de ese cliente. Prueba otro valor. Si el cliente no tiene pedidos coincidentes, se obtiene un conjunto de resultados vacío; eso no significa necesariamente que el procedimiento haya fallado.

Los parámetros permiten reutilizar el mismo procedimiento con distintas entradas. La instrucción SQL permanece fija y solo cambia el valor. Esto es más fácil de revisar que construir una instrucción nueva concatenando cadenas, y evita interpretar un valor del usuario como código SQL. El código de una aplicación también debe usar los parámetros del controlador de base de datos en vez de insertar valores en el texto SQL. Este ejemplo usa un parámetro entero sencillo; el SQL dinámico avanzado queda fuera de esta introducción.

Si no proporcionas un parámetro obligatorio y no se definió un valor predeterminado, SQL Server avisará que falta. Un procedimiento puede tener valores predeterminados, pero deben representar una decisión de negocio clara. No añadas un valor solo para hacer desaparecer un error: podrías consultar datos equivocados o ampliar una operación sin querer.

Leer y probar el resultado

Revisa las filas para CustomerId = 11 y compáralas con un SELECT directo que tenga el mismo filtro. Prueba un cliente con varios pedidos y, si los datos de ejemplo incluyen uno, otro cliente sin pedidos. Comprueba el resultado esperado y también el comportamiento cuando no hay coincidencias.

El nombre del procedimiento explica la intención, pero no garantiza que el resultado sea correcto. Si cambia una tabla, quizá haya que actualizar el procedimiento. Los nombres y tipos de parámetros y las columnas de salida forman parte de la interfaz para quien lo utiliza. Elige esos elementos deliberadamente y prueba el procedimiento después de modificarlo.

Este ejemplo de solo lectura no cambia filas. Otros procedimientos pueden insertar, actualizar o eliminar datos. Lee la definición y confirma sus efectos antes de ejecutar un procedimiento desconocido, sobre todo en una base compartida o de producción. Sigue el proceso de revisión del equipo.

Cambiar un procedimiento con ALTER PROCEDURE

Usa ALTER PROCEDURE para sustituir la definición sin cambiar la identidad del objeto. Esta versión también devuelve el nombre del cliente mediante una unión con dbo.Customers:

Consulta SQL: Cambiar un procedimiento con ALTER PROCEDURET-SQL
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT c.CustomerId, c.FirstName, c.LastName,
           o.OrderId, o.OrderDate, o.Status, o.Amount
    FROM dbo.Customers AS c
    INNER JOIN dbo.Orders AS o
        ON o.CustomerId = c.CustomerId
    WHERE c.CustomerId = @CustomerId
    ORDER BY o.OrderDate DESC, o.OrderId DESC;
END;
T-SQL14 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Ejecuta toda la definición ALTER, vuelve a llamar al procedimiento y compara sus columnas y filas con lo que esperan sus consumidores. Cambiar la forma del resultado puede afectar a aplicaciones, informes y otros procedimientos. Los nombres y tipos de los parámetros también son parte de la interfaz; coordina los cambios si ya hay consumidores.

Procedimiento o vista

Una vista es una consulta con nombre que se lee con SELECT. Puede ser útil cuando varios consumidores necesitan un conjunto de filas compartido, como dbo.vw_CustomerOrders. Un procedimiento se ejecuta con EXEC, puede recibir parámetros, devolver un conjunto de resultados y realizar otras operaciones. La vista es una fuente de consulta; el procedimiento es una operación que se invoca.

Elige una vista cuando el objetivo principal sea ofrecer una consulta reutilizable que otras consultas puedan filtrar o unir. Elige un procedimiento cuando el consumidor necesite una operación con nombre y entradas definidas o una secuencia de instrucciones. Ambos ayudan a centralizar SQL, pero ninguno debe ocultar una lógica difícil de entender. Consulta Vistas en SQL Server para revisar el ejemplo anterior.

Permisos: conceptos básicos

Los permisos de la base determinan quién puede ejecutar un procedimiento y quién puede leer o modificar las tablas subyacentes. Un administrador puede conceder el permiso EXECUTE a un usuario o rol de aplicación. El diseño exacto depende de la propiedad, los esquemas, el despliegue y las políticas de la organización; debe revisarlo el responsable de la base.

No supongas que el código se vuelve seguro por estar dentro de un procedimiento. Tanto el diseño como los permisos deben ser adecuados. No concedas permisos amplios, como la propiedad completa de la base, solo para hacer funcionar un ejemplo. Para aprender, ejecútalo con tu cuenta local de práctica en JaviBuildsLab.

Qué esperar del rendimiento y el mantenimiento

Un procedimiento almacenado no es automáticamente más rápido que una consulta equivalente. Puede mejorar la reutilización y la coherencia, pero SQL Server aún debe ejecutar las instrucciones sobre los datos disponibles. El rendimiento depende de la consulta, el esquema, los índices, el volumen y la carga de trabajo. Primero procura que la lógica sea correcta y comprensible; mide el rendimiento real cuando exista una necesidad.

Un procedimiento tampoco sustituye un buen diseño de consultas. Un JOIN confuso, un filtro ausente o columnas innecesarias siguen siendo problemas aunque se oculten dentro de un procedimiento. Asigna tipos útiles a los parámetros, devuelve solo las columnas que necesita el consumidor y documenta las suposiciones importantes. No añadas capas de procedimientos sin un motivo claro.

Práctica y siguiente paso

Crea el procedimiento en la base de práctica, ejecútalo con el cliente 11 y compara la salida con un SELECT directo. Modifícalo para incluir el nombre del cliente, vuelve a ejecutarlo y observa qué cambia. Antes de ejecutar un procedimiento escrito por otra persona, revisa su definición y determina si lee o modifica datos.

  1. El usuario proporciona @CustomerId
  2. EXEC el procedimiento almacenado
  3. SELECT devuelve los pedidos
Llamar a un procedimiento

Los procedimientos agrupan una operación con nombre. La siguiente lección trata las claves que conectan filas de clientes y pedidos y ayudan a SQL Server a proteger esa relación: claves primarias y externas. Continúa con la hoja de ruta de SQL Server Foundations.

Comprueba lo que has aprendido

2 preguntas rápidas

Pregunta 1 de 2¿Qué es un procedimiento almacenado?
Pregunta 2 de 2¿Cómo puede recibir datos de entrada un procedimiento?

Fundamentos de SQL Server

Lección 22 de 25

Continuar a la siguiente lección

Claves primarias y claves externas en SQL Server

Continuar a la lección 23

Etiquetas

  • SQL Server
  • Fundamentos de SQL
  • SSMS
  • T-SQL