Comenzando con los procedimientos almacenados en SQL Server

Si has estado utilizando SQL Server durante algún tiempo, es posible que hayas notado que todo el código emitido contra la base de datos está incrustado en el código de la aplicación. Sin embargo, SQL Server proporciona una potente función llamada procedimientos almacenados que puede ayudarte a organizar y administrar tu código de base de datos de manera más eficiente.

Un procedimiento almacenado es un conjunto de declaraciones T-SQL que se almacenan en la base de datos. En lugar de emitir múltiples declaraciones desde tu aplicación, simplemente puedes llamar a un procedimiento almacenado para ejecutar un conjunto de tareas. Esto no solo simplifica el código de tu aplicación, sino que también te permite reutilizar el mismo conjunto de código en diferentes aplicaciones o ventanas de consulta.

Echemos un vistazo a algunos ejemplos simples para entender cómo crear y usar procedimientos almacenados en SQL Server.

Ejemplo 1 – Procedimiento almacenado simple

En este ejemplo, crearemos un procedimiento almacenado simple que recupera el primer registro de la tabla Person.Contact en la base de datos AdventureWorks:

CREATE PROCEDURE uspGetContact 
AS 
SELECT TOP 1 ContactID, FirstName, LastName 
FROM Person.Contact

Para ejecutar este procedimiento almacenado, puedes usar el siguiente comando:

EXEC uspGetContact

Esto devolverá los resultados de la consulta.

Ejemplo 2 – Procedimiento almacenado con un parámetro

En este ejemplo, modificaremos el procedimiento almacenado anterior para aceptar un parámetro que seleccione dinámicamente los registros. Usaremos la declaración ALTER PROCEDURE para modificar el procedimiento existente:

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
SELECT TOP 1 ContactID, FirstName, LastName 
FROM Person.Contact 
WHERE LastName = @LastName

Puedes ejecutar el procedimiento almacenado con cualquiera de los siguientes comandos:

EXEC uspGetContact 'Alberts'

EXEC uspGetContact @LastName='Alberts'

Ambos comandos devolverán los resultados según el valor del parámetro.

Ejemplo 3 – Procedimiento almacenado con un parámetro y parámetro de salida

En este ejemplo, agregaremos un parámetro de salida al procedimiento almacenado. El parámetro de salida se utilizará para devolver el ContactID que estamos buscando en el procedimiento almacenado. También uniremos varias tablas para recuperar información adicional:

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output 
AS 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName

Después de modificar el procedimiento almacenado, puedes ejecutarlo utilizando el siguiente código:

DECLARE @ContactID INT 
SET @ContactID = 0 

EXEC uspGetContact @LastName='Smith', @ContactID=@ContactID OUTPUT 

IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END

Esto ejecutará el procedimiento almacenado y devolverá la información de la persona y la dirección según el ContactID.

Ejemplo 4 – Procedimiento almacenado utilizando la declaración RAISERROR

En este ejemplo, combinaremos los pasos del Ejemplo 3 en un solo procedimiento almacenado. También utilizaremos la declaración RAISERROR para devolver un error si no se encuentran registros:

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
DECLARE @ContactID INT 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName 

IF @@ROWCOUNT > 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 
    
   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No se encontraron registros',10,1) 
END

EXEC uspGetContact @LastName='Walters'

Esto ejecutará el procedimiento almacenado y devolverá la información de la persona y la dirección si se encuentran registros. De lo contrario, generará un error.

Ejemplo 5 – Procedimiento almacenado con un procedimiento almacenado de llamada separado

En este ejemplo, crearemos dos procedimientos almacenados. El primer procedimiento almacenado, uspFindContact, buscará el primer registro que tenga un registro de dirección y devolverá el ContactID al procedimiento almacenado de llamada, uspGetContact, para mostrar la información de la persona y la dirección:

CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output 
AS 
SELECT TOP 1 @ContactID = c.ContactID 
FROM HumanResources.Employee a  
INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
WHERE c.LastName = @LastName

El código a continuación modifica el procedimiento almacenado uspGetContact para llamar a uspFindContact y devolver los conjuntos de registros:

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
DECLARE @ContactID INT 
SET @ContactID = 0 

EXEC uspFindContact @LastName=@LastName, @ContactID=@ContactID OUTPUT 

IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No se encontraron registros',10,1) 
END

EXEC uspGetContact @LastName='Walters'

Esto ejecutará el procedimiento almacenado y devolverá la información de la persona y la dirección según el ContactID.

Ejemplo 6 – Procedimiento almacenado con comentarios

En este último ejemplo, agregaremos comentarios al código del procedimiento almacenado para proporcionar explicaciones y hacer que el código sea más legible. Los comentarios se pueden agregar utilizando la sintaxis “–” o “/* */”:

ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) 
AS 
/* Este es un procedimiento almacenado de muestra para mostrar 
   cómo funcionan los comentarios dentro de un procedimiento almacenado */ 

-- declarar variable 
DECLARE @ContactID INT 
-- establecer valor de variable 
SET @ContactID = 0 

-- ejecutar procedimiento almacenado y devolver valor de ContactID 
EXEC uspFindContact @LastName=@LastName, @ContactID=@ContactID OUTPUT 

-- si ContactID no es igual a 0, devolver datos; de lo contrario, devolver error 
IF @ContactID <> 0 
BEGIN 
   SELECT ContactID, FirstName, LastName 
   FROM Person.Contact 
   WHERE ContactID = @ContactID 

   SELECT d.AddressLine1, d.City, d.PostalCode 
   FROM HumanResources.Employee a  
   INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID 
   INNER JOIN Person.Contact c ON a.ContactID = c.ContactID 
   INNER JOIN Person.Address d ON b.AddressID = d.AddressID 
   WHERE c.ContactID = @ContactID 
END 
ELSE 
BEGIN 
   RAISERROR ('No se encontraron registros',10,1) 
END

Estos son ejemplos simples, pero demuestran lo fácil que es crear y usar procedimientos almacenados en SQL Server. Al aprovechar los procedimientos almacenados, puedes mejorar el rendimiento, la seguridad y el mantenimiento de tu código de base de datos.

Si puedes ejecutar una declaración SELECT desde una ventana de consulta o tu aplicación, también puedes ejecutar un procedimiento almacenado de la misma manera que se muestra arriba.

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on