En este artículo, aprenderemos cómo crear procedimientos almacenados en SQL Server con diferentes ejemplos. Un procedimiento almacenado de SQL Server es un conjunto de declaraciones agrupadas como una unidad lógica y almacenadas en la base de datos. El procedimiento almacenado acepta parámetros y ejecuta las declaraciones T-SQL en el procedimiento, devuelve el conjunto de resultados si los hay.

Beneficios de usar un procedimiento almacenado

Hay varios beneficios de usar procedimientos almacenados en SQL Server:

• Modificación fácil: Los procedimientos almacenados se pueden modificar fácilmente sin necesidad de reiniciar o implementar la aplicación. Esto se debe a que el código se almacena en la base de datos, lo que permite realizar modificaciones simples mediante la declaración ALTER PROCEDURE.
• Reducción del tráfico de red: Al utilizar procedimientos almacenados, solo se pasa el nombre del procedimiento por la red en lugar de todo el código T-SQL. Esto ayuda a reducir el tráfico de red y mejorar el rendimiento.
• Reutilización: Los procedimientos almacenados se pueden ejecutar por varios usuarios o aplicaciones de cliente sin necesidad de volver a escribir el código.
• Seguridad: Los procedimientos almacenados reducen la amenaza al eliminar el acceso directo a las tablas. También se pueden cifrar para proteger el código fuente.
• Rendimiento: Cuando se ejecuta un procedimiento almacenado por primera vez, crea un plan y lo almacena en el búfer de caché. Esto permite reutilizar el plan, lo que resulta en un mejor rendimiento.

Creación de un procedimiento almacenado simple

Comencemos creando un procedimiento almacenado simple que une dos tablas y devuelve el conjunto de resultados:

CREATE PROCEDURE GetProductDesc
AS
BEGIN
  SET NOCOUNT ON
  SELECT P.ProductID, P.ProductName, PD.ProductDescription
  FROM Product P
  INNER JOIN ProductDescription PD ON P.ProductID = PD.ProductID
END

Podemos usar la declaración EXEC para ejecutar el procedimiento almacenado:

EXEC GetProductDesc

El conjunto de resultados se mostrará como salida.

Creación de un procedimiento almacenado con parámetros

También podemos crear un procedimiento almacenado que acepte parámetros de entrada y procese registros en función de esos parámetros. Aquí tienes un ejemplo:

CREATE PROCEDURE GetProductDesc_withparameters
  @PID INT
AS
BEGIN
  SET NOCOUNT ON
  SELECT P.ProductID, P.ProductName, PD.ProductDescription
  FROM Product P
  INNER JOIN ProductDescription PD ON P.ProductID = PD.ProductID
  WHERE P.ProductID = @PID
END

Cuando ejecutamos el procedimiento almacenado, debemos pasar el parámetro de entrada:

EXEC GetProductDesc_withparameters 706

El conjunto de resultados se filtrará en función del valor del parámetro de entrada.

Creación de un procedimiento almacenado con valores de parámetro predeterminados

También podemos establecer valores de parámetro predeterminados para un procedimiento almacenado. Aquí tienes un ejemplo:

CREATE PROCEDURE GetProductDesc_withDefaultparameters
  @PID INT = 706
AS
BEGIN
  SET NOCOUNT ON
  SELECT P.ProductID, P.ProductName, PD.ProductDescription
  FROM Product P
  INNER JOIN ProductDescription PD ON P.ProductID = PD.ProductID
  WHERE P.ProductID = @PID
END

Cuando ejecutamos el procedimiento anterior sin pasar un valor de parámetro, se utilizará el valor predeterminado (706). Sin embargo, cuando se pasa un valor, se ignorará el valor predeterminado.

Creación de un procedimiento almacenado con un parámetro de salida

También podemos crear un procedimiento almacenado con un parámetro de salida. Esto nos permite recuperar un valor del procedimiento almacenado. Aquí tienes un ejemplo:

CREATE TABLE Employee
(
  EmpID int identity(1, 1),
  EmpName varchar(500)
)

CREATE PROCEDURE ins_NewEmp_with_outputparamaters
  @Ename varchar(50),
  @EId int output
AS
BEGIN
  SET NOCOUNT ON
  INSERT INTO Employee (EmpName) VALUES (@Ename)
  SELECT @EId = SCOPE_IDENTITY()
END

Cuando ejecutamos el procedimiento almacenado, debemos declarar una variable para almacenar el valor devuelto por el parámetro de salida:

DECLARE @EmpID INT
EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT
SELECT @EmpID

El valor del parámetro de salida se mostrará como salida.

Creación de un procedimiento almacenado cifrado

Podemos ocultar el código fuente en un procedimiento almacenado creándolo con la opción “ENCRYPTION”. Aquí tienes un ejemplo:

CREATE PROCEDURE GetEmployees WITH ENCRYPTION
AS
BEGIN
  SET NOCOUNT ON
  SELECT EmpID, EmpName FROM Employee
END

Cuando intentamos ver el código del procedimiento almacenado cifrado usando sp_helptext, devolverá “El texto del objeto ‘GetEmployees’ está cifrado”. Además, intentar generar el script del procedimiento almacenado cifrado desde SQL Server Management Studio dará como resultado un error.

Creación de un procedimiento temporal

Al igual que las tablas temporales, también podemos crear procedimientos temporales. Hay dos tipos de procedimientos temporales: procedimientos almacenados temporales locales y procedimientos almacenados temporales globales.

Procedimientos almacenados temporales locales: Se crean con un prefijo “#” y solo se pueden acceder en la sesión donde se crean. Se eliminan automáticamente cuando se cierra la conexión. Aquí tienes un ejemplo:

CREATE PROCEDURE #Temp
AS
BEGIN
  PRINT 'Procedimiento temporal local'
END

Procedimientos almacenados temporales globales: Estos procedimientos se crean con un prefijo “##” y también se pueden acceder en otras sesiones. También se eliminan automáticamente cuando se cierra la conexión utilizada para crear el procedimiento. Aquí tienes un ejemplo:

CREATE PROCEDURE ##TEMP
AS
BEGIN
  PRINT 'Procedimiento temporal global'
END

Modificación del procedimiento almacenado

Para modificar un procedimiento almacenado existente, podemos usar la declaración ALTER PROCEDURE. Aquí tienes un ejemplo:

ALTER PROCEDURE GetProductDesc
AS
BEGIN
  SET NOCOUNT ON
  SELECT P.ProductID, P.ProductName, PD.ProductDescription
  FROM Product P
  INNER JOIN ProductDescription PD ON P.ProductID = PD.ProductID
  WHERE P.ProductID > 500
END

El procedimiento almacenado se modificará para devolver solo los registros con un ProductID mayor que 500.

Cambio de nombre del procedimiento almacenado

Para cambiar el nombre de un procedimiento almacenado utilizando T-SQL, podemos usar el procedimiento almacenado del sistema sp_rename. Aquí tienes un ejemplo:

EXEC sp_rename 'GetProductDesc', 'GetProductDesc_new'

El procedimiento almacenado “GetProductDesc” se cambiará de nombre a “GetProductDesc_new”.

Conclusión

En este artículo, exploramos los procedimientos almacenados de SQL Server con diferentes ejemplos. Los procedimientos almacenados ofrecen varios beneficios, como una modificación fácil, una reducción del tráfico de red, la reutilización, la seguridad y un mejor rendimiento. Se pueden crear con parámetros, valores de parámetro predeterminados y parámetros de salida. Además, aprendimos sobre procedimientos almacenados cifrados, procedimientos temporales, modificación de procedimientos existentes y cambio de nombre de procedimientos.

Si tienes alguna pregunta, no dudes en hacerla en la sección de comentarios a continuación.