Creación de procedimientos almacenados
Los procedimientos almacenados son una de las herramientas más eficaces de SQL Server para encapsular la lógica de negocios y mejorar el rendimiento de las aplicaciones. Al crear procedimientos almacenados, se crean bloques de código reutilizables que se ejecutan en el servidor, lo que reduce el tráfico de red y centraliza la lógica de acceso a datos.
Comprender los procedimientos almacenados
Un procedimiento almacenado es una colección compilada de instrucciones T-SQL que SQL Server almacena y ejecuta como una sola unidad. A diferencia de las consultas no planeadas que se envían al servidor cada vez, los procedimientos almacenados se precompilan y optimizan, lo que significa que se ejecutan más rápido en ejecuciones posteriores.
Use procedimientos almacenados para encapsular lógica de negocios compleja, aplicar reglas de validación de datos y controlar cómo interactúan las aplicaciones con la base de datos. Por ejemplo, en lugar de permitir el acceso directo a tablas, puede crear procedimientos almacenados que validen la entrada, apliquen reglas de negocio y registren cambios antes de modificar los datos.
Las ventajas de rendimiento proceden del almacenamiento en caché del plan de consulta. Con las consultas no planeadas, SQL Server debe analizar y optimizar cada consulta cada vez. Con los procedimientos almacenados, el plan de ejecución se almacena en caché después de la primera ejecución, lo que reduce la sobrecarga de las operaciones repetidas.
Crea procedimientos almacenados básicos
La creación de un procedimiento almacenado comienza con la instrucción CREATE PROCEDURE seguida de tu lógica T-SQL. Especifique el nombre del procedimiento mediante un identificador calificado por esquemas, lo que mejora la claridad y el rendimiento.
CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;
END
La SET NOCOUNT ON instrucción impide que el mensaje sobre el número de filas afectadas se envíe al cliente. Esto reduce el tráfico de red y mejora el rendimiento, especialmente cuando el procedimiento ejecuta varias instrucciones.
Al crear procedimientos, use las BEGIN palabras clave y END para definir claramente el cuerpo del procedimiento. Esto hace que el código sea más legible y ayuda a evitar errores al agregar o modificar lógica más adelante.
Trabajar con parámetros
Los parámetros hacen que los procedimientos almacenados sean flexibles y reutilizables. Los parámetros de entrada se definen para aceptar valores de la aplicación que realiza la llamada y los parámetros de salida para devolver valores al autor de la llamada.
Los parámetros de entrada usan el símbolo @ seguido de un nombre de parámetro y un tipo de datos. Puede proporcionar valores predeterminados para que los parámetros sean opcionales:
CREATE PROCEDURE dbo.GetCustomerOrdersByDate
@CustomerID int,
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT
OrderID,
OrderDate,
TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END
Los parámetros de salida permiten devolver valores a la aplicación que llama. Los define con la OUTPUT palabra clave :
CREATE PROCEDURE dbo.CalculateOrderTotal
@OrderID int,
@TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalAmount = SUM(Quantity * UnitPrice)
FROM dbo.OrderDetails
WHERE OrderID = @OrderID;
RETURN 0;
END
Al llamar a un procedimiento con parámetros de salida, debe declarar una variable para recibir el valor y usar la OUTPUT palabra clave en la EXECUTE instrucción .
Implementación del control de errores
Los procedimientos almacenados robustos incluyen el manejo de errores para gestionar condiciones inesperadas y mantener la integridad de los datos. El control de errores se implementa mediante TRY...CATCH bloques, que funcionan de forma similar al control de excepciones en otros lenguajes de programación.
CREATE PROCEDURE dbo.InsertCustomerOrder
@CustomerID int,
@OrderDate datetime,
@TotalAmount decimal(10,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
RAISERROR('Customer does not exist.', 16, 1);
END
-- Insert order
INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
COMMIT TRANSACTION;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity int = ERROR_SEVERITY();
DECLARE @ErrorState int = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN -1;
END CATCH
END
El TRY bloque contiene la lógica principal, mientras que el CATCH bloque controla los errores que se producen. Puede usar funciones del sistema como ERROR_MESSAGE(), ERROR_SEVERITY()y ERROR_STATE() para capturar los detalles del error y pasarlos a la aplicación que llama.
Siempre compruebe @@TRANCOUNT antes de revertir las transacciones en el bloque CATCH. Esto evita errores si la transacción ya se completó o nunca se inició.
Aplicación de procedimientos recomendados
Siguiendo las mejores prácticas establecidas al crear procedimientos almacenados, se garantiza que son mantenibles, seguras y con buen rendimiento.
Use nombres aptos para el esquema
Use nombres aptos para el esquema para todos los objetos. Esto elimina la ambigüedad y mejora el rendimiento evitando la sobrecarga de resolución de esquemas:
-- Good
SELECT * FROM dbo.Orders
-- Avoid
SELECT * FROM Orders
Implementación de la validación de parámetros
Implemente la validación de parámetros al principio del procedimiento. Falla rápidamente cuando los datos de entrada no son válidos en lugar de procesar datos incorrectos.
IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
RAISERROR('CustomerID must be a positive integer.', 16, 1);
RETURN -1;
END
Evitar SELECT *
Evite SELECT * en el código de producción. Enumere explícitamente las columnas para evitar problemas cuando cambian las estructuras de tabla y para mejorar el rendimiento de las consultas:
-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders
-- Avoid
SELECT * FROM dbo.Orders
Uso de nombres significativos
Use nombres significativos que describan lo que hace el procedimiento. Incluya un verbo que indique la operación (Get, Insert, Update, Delete, Calculate):
CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders
Evitar el sp_ prefijo
No use el sp_ prefijo para los procedimientos almacenados. SQL Server reserva este prefijo para los procedimientos del sistema almacenados en la master base de datos. Al asignar un nombre a un procedimiento con sp_, SQL Server primero busca master antes de comprobar la base de datos actual, agregando una sobrecarga innecesaria:
-- Good
CREATE PROCEDURE dbo.GetCustomerOrders
-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders
La creación de estas prácticas le ayuda a crear procedimientos almacenados que el equipo pueda comprender, mantener y confiar para realizar de forma confiable en entornos de producción.