Microsoft SQL Server
Procedura di archiviazione

introduzione

Sintassi

• CREA {PROCEDURA | PROC} [nome_schema.] Nome_procedura
• [@parameter [tipo_schema_nome.] tipo di dati
• [VARYING] [= valore predefinito] [OUT | OUTPUT | SOLA LETTURA ]
• , @parameter [tipo_schema_nome.] tipo di dati
• [VARYING] [= valore predefinito] [OUT | OUTPUT | SOLA LETTURA ] ]
• [CON {ENCRYPTION | RECOMPILE | ESEGUI COME clausola}]
• [PER RISPOSTA]
• COME
• INIZIO
• [Declaration_section]
• executable_section
• FINE;

Creazione ed esecuzione di una stored procedure di base

Utilizzando la tabella Authors nel database della libreria

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

È possibile eseguire una procedura con alcune sintassi diverse. Innanzitutto, è possibile utilizzare EXECUTE o EXEC

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

Inoltre, è possibile omettere il comando EXEC. Inoltre, non è necessario specificare quale parametro si sta passando, come si passa in tutti i parametri.

GetName NULL, 'Ernest Hemingway'

Quando si desidera specificare i parametri di input in un ordine diverso da come sono dichiarati nella procedura, è possibile specificare il nome del parametro e assegnare i valori. Per esempio

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

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

l'ordine normale per eseguire questa procedura è specificare il valore per @ Param1 prima e poi @ Param2 secondo. Quindi assomiglierà a qualcosa del genere

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

Ma è anche possibile che tu possa usare quanto segue

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

in questo, si specifica il valore per @ param2 first e @ Param1 second. Il che significa che non devi mantenere lo stesso ordine che è stato dichiarato nella procedura, ma puoi avere qualsiasi ordine come desideri. ma dovrai specificare a quale parametro stai impostando il valore

Accedi alla stored procedure da qualsiasi database

Inoltre, è possibile creare una procedura con un prefisso sp_ questi procuedres, come tutte le stored procedure di sistema, possono essere eseguite senza specificare il database a causa del comportamento predefinito di SQL Server. Quando si esegue una stored procedure che inizia con "sp_", SQL Server cerca prima la procedura nel database master. Se la procedura non viene trovata in master, viene visualizzata nel database attivo. Se si dispone di una stored procedure a cui si desidera accedere da tutti i database, crearla in master e utilizzare un nome che includa il prefisso "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

PROCEDURA MEMORIZZATA con parametri OUT

Le stored procedure possono restituire valori utilizzando la parola chiave OUTPUT nel relativo elenco di parametri.

Creazione di una stored procedure con un singolo parametro out

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

Esecuzione della stored procedure

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

Creazione di una stored procedure con più parametri out

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

Esecuzione della stored procedure

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

Procedura memorizzata con If ... Else e Insert Into operation

Crea tabella di esempio Employee :

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

Crea una stored procedure che controlla se i valori passati nella stored procedure non sono nulli o non vuoti e eseguono operazioni di inserimento nella tabella Employee.

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

Esegui la stored procedure

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

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

SQL dinamico in stored procedure

Dynamic SQL ci consente di generare ed eseguire istruzioni SQL in fase di esecuzione. SQL dinamico è necessario quando le nostre istruzioni SQL contengono identificatori che possono cambiare in tempi di compilazione diversi.

Semplice esempio di SQL dinamico:

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

Nella query sql sopra, possiamo vedere che possiamo usare sopra la query definendo i valori in @table_name, @col_name, and @col_value in fase di esecuzione. La query viene generata in fase di esecuzione ed eseguita. Questa è la tecnica in cui possiamo creare interi script come stringa in una variabile ed eseguirla. Possiamo creare query più complesse utilizzando il concetto dinamico di SQL e concatenazione. Questo concetto è molto potente quando si desidera creare uno script che può essere utilizzato in diverse condizioni.

Esecuzione della stored procedure

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

Tabella che ho usato

Produzione

Semplice loop

Prima consente di recuperare alcuni dati in una tabella temporanea denominata #systables e annunci un numero di riga incrementale in modo che possiamo interrogare un record alla volta

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

Quindi dichiariamo alcune variabili per controllare il ciclo e memorizzare il nome della tabella in questo esempio

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

Ora possiamo effettuare il loop usando un po 'di tempo. @rn select , ma questa potrebbe anche essere un'istruzione separata per ex set @rn = @rn + 1 che dipenderà dalle vostre esigenze. Usiamo anche il valore di @rn prima che venga incrementato per selezionare un singolo record da #systables . Infine stampiamo il nome della tabella.

while @rn <= @maxRn
    begin

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

        print @tablename
    end

Semplice loop

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