Microsoft SQL Server
Procedimientos almacenados

Introducción

Sintaxis

• CREAR {PROCEDIMIENTO | PROC} [schema_name.] Procedure_name
• [@parameter [type_schema_name.] datatype
• [VARYING] [= predeterminado] [OUT | SALIDA | SOLO LECTURA ]
• , @parameter [type_schema_name.] datatype
• [VARYING] [= predeterminado] [OUT | SALIDA | SOLO LECTURA ] ]
• [CON {ENCRYPTION | RECOMPILE | EJECUTAR COMO Cláusula}]
• [PARA REPLICACIÓN]
• COMO
• EMPEZAR
• [declaración_sección]
• sección ejecutable
• FIN;

Creación y ejecución de un procedimiento almacenado básico.

Usando la tabla de Authors en la base de datos de la biblioteca

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

Puede ejecutar un procedimiento con algunas sintaxis diferentes. Primero, puedes usar EXECUTE o EXEC

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

Además, puede omitir el comando EXEC. Además, no tiene que especificar qué parámetro está pasando, ya que pasa en todos los parámetros.

GetName NULL, 'Ernest Hemingway'

Cuando desee especificar los parámetros de entrada en un orden diferente al de cómo se declaran en el procedimiento, puede especificar el nombre del parámetro y asignar valores. Por ejemplo

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

el orden normal para ejecutar este procedimiento es especificar el valor para @ Param1 primero y luego @ Param2 segundo. Así se verá algo como esto

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

Pero también es posible que puedas usar lo siguiente.

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

en esto, está especificando el valor para @ param2 primero y @ Param1 segundo. Lo que significa que no tiene que mantener el mismo orden que se declara en el procedimiento, pero puede tener cualquier orden que desee. pero tendrá que especificar a qué parámetro está configurando el valor

Acceda al procedimiento almacenado desde cualquier base de datos.

Y también puede crear un procedimiento con un prefijo sp_ estos procedimientos, como todos los procedimientos almacenados del sistema, se pueden ejecutar sin especificar la base de datos debido al comportamiento predeterminado de SQL Server. Cuando ejecuta un procedimiento almacenado que comienza con "sp_", SQL Server busca primero el procedimiento en la base de datos maestra. Si el procedimiento no se encuentra en el maestro, busca en la base de datos activa. Si tiene un procedimiento almacenado al que desea acceder desde todas sus bases de datos, créelo en el maestro y use un nombre que incluya el prefijo "sp_".

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

PROCEDIMIENTO ALMACENADO con parámetros OUT

Los procedimientos almacenados pueden devolver valores utilizando la palabra clave OUTPUT en su lista de parámetros.

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

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

Ejecutando el procedimiento almacenado

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

Creación de un procedimiento almacenado con múltiples parámetros de salida.

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

Ejecutando el procedimiento almacenado

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

Procedimiento almacenado con If ... Else e Insertar en operación

Crear tabla de ejemplo Employee :

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

Crea un procedimiento almacenado que verifica si los valores pasados ​​en el procedimiento almacenado no son nulos o no están vacíos y realizan una operación de inserción en la tabla Empleado.

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

Ejecutar el procedimiento almacenado.

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

SQL dinámico en procedimiento almacenado

El SQL dinámico nos permite generar y ejecutar sentencias de SQL en tiempo de ejecución. SQL dinámico es necesario cuando nuestras sentencias de SQL contienen un identificador que puede cambiar en diferentes tiempos de compilación.

Ejemplo simple de SQL dinámico:

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

En la consulta SQL anterior, podemos ver que podemos usar la consulta anterior definiendo valores en @table_name, @col_name, and @col_value en tiempo de ejecución. La consulta se genera en tiempo de ejecución y se ejecuta. Esta es una técnica en la que podemos crear scripts completos como una cadena en una variable y ejecutarla. Podemos crear consultas más complejas utilizando SQL dinámico y el concepto de concatenación. Este concepto es muy poderoso cuando se desea crear un script que se pueda usar bajo varias condiciones.

Ejecutando procedimiento almacenado

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

Tabla que he usado

Salida

Bucle simple

Primero obtengamos algunos datos en una tabla temporal llamada #systables y un número de fila incremental para que podamos consultar un registro a la vez

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

A continuación declaramos algunas variables para controlar el bucle y almacenar el nombre de la tabla en este ejemplo.

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

Ahora podemos hacer un bucle usando un simple tiempo. Incrementamos @rn en la declaración de select , pero esto también podría haber sido una declaración separada para el set @rn = @rn + 1 ex set @rn = @rn + 1 , dependerá de sus requisitos. También usamos el valor de @rn antes de que se incremente para seleccionar un solo registro de #systables . Por último imprimimos el nombre de la tabla.

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

Bucle simple

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO