MENU
  1. ホーム
  2. SQL
  3. Guida Completa ai Parametri delle Procedure SQL: Utilizzo e Best Practices

Guida Completa ai Parametri delle Procedure SQL: Utilizzo e Best Practices

SQL

L’utilizzo dei parametri nelle procedure SQL consente una manipolazione dei dati efficiente e flessibile. Le procedure memorizzate semplificano operazioni complesse all’interno del database, migliorando la riusabilità e la manutenibilità. In questo articolo, esamineremo in dettaglio i tipi di parametri, come dichiararli, esempi pratici, gestione degli errori e tutto ciò che riguarda i parametri delle procedure memorizzate.

Indice

Fondamenti delle Procedure Memorizzate

Una procedura memorizzata è un insieme di istruzioni SQL che viene eseguito all’interno del database e può essere richiamato come una funzione. Questo permette di gestire query complesse e attività ripetitive in modo efficiente, migliorando la riusabilità e la manutenibilità. Le procedure memorizzate contribuiscono anche all’ottimizzazione delle prestazioni e al rafforzamento della sicurezza.

Vantaggi delle Procedure Memorizzate

I principali vantaggi dell’utilizzo delle procedure memorizzate sono i seguenti:

  1. Riusabilità: Una volta create, possono essere riutilizzate più volte.
  2. Prestazioni: Poiché sono precompilate, la velocità di esecuzione è più rapida.
  3. Sicurezza: Manipolando i dati attraverso la procedura piuttosto che eseguendo direttamente query SQL, si riduce il rischio di SQL injection.
  4. Manutenibilità: Centralizzando la logica, la manutenzione è più semplice.

Sintassi di Base delle Procedure Memorizzate

Di seguito è riportata la sintassi di base per creare una procedura memorizzata in SQL Server:

CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- Inserisci qui le istruzioni SQL
END;

Come esempio concreto, creiamo una procedura memorizzata semplice per ottenere dati dalla tabella dei dipendenti.

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

Questa procedura memorizzata, una volta eseguita, recupera tutti i record dalla tabella dei dipendenti.

Tipi di Parametri e Caratteristiche

Le procedure memorizzate possono utilizzare più parametri per eseguire operazioni sui dati in modo flessibile. Esistono tre tipi principali di parametri: parametri di input, parametri di output e parametri di input-output. Esaminiamo in dettaglio le caratteristiche e gli usi di ciascuno di essi.

Parametri di Input

I parametri di input vengono utilizzati per passare valori alla procedura memorizzata. Ricevono valori dall’utente e li utilizzano per elaborare i dati. Per dichiararli si utilizza la parola chiave IN.

Esempio:

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

In questo esempio, viene utilizzato un parametro di input chiamato @EmployeeID per ottenere informazioni su un dipendente specifico.

Parametri di Output

I parametri di output vengono utilizzati per restituire il risultato dell’esecuzione della procedura memorizzata al chiamante. Per dichiararli si utilizza la parola chiave OUT.

Esempio:

CREATE PROCEDURE GetEmployeeCount
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) FROM Employees;
END;

In questo esempio, un parametro di output chiamato @EmployeeCount viene utilizzato per ottenere e restituire il numero di dipendenti al chiamante.

Parametri di Input-Output

I parametri di input-output vengono utilizzati per passare un valore alla procedura memorizzata e restituire il valore aggiornato dopo l’elaborazione. Per dichiararli si utilizza la parola chiave INOUT.

Esempio:

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2) OUTPUT
AS
BEGIN
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
    SELECT @NewSalary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
END;

In questo esempio, un parametro di input-output chiamato @NewSalary viene utilizzato per aggiornare lo stipendio di un dipendente e restituire il valore aggiornato.

Dichiarazione e Utilizzo dei Parametri

Spieghiamo come dichiarare e utilizzare i parametri all’interno delle procedure memorizzate con esempi concreti. Dichiarare e utilizzare correttamente i parametri migliora notevolmente la flessibilità e la riusabilità delle procedure memorizzate.

Dichiarazione dei Parametri

Quando si dichiarano parametri all’interno di una procedura memorizzata, è necessario specificare il nome del parametro, il tipo di dati e, se necessario, la direzione (input, output, input-output).

Sintassi di base:

CREATE PROCEDURE ProcedureName
    @ParameterName DataType [IN | OUT | INOUT]
AS
BEGIN
    -- Inserisci qui le istruzioni SQL
END;

Esempio:

CREATE PROCEDURE GetEmployeeByName
    @EmployeeName NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Employees WHERE Name = @EmployeeName;
END;

In questo esempio, viene dichiarato un parametro chiamato @EmployeeName e utilizzato per cercare i dati dei dipendenti in base al nome.

Utilizzo dei Parametri

I parametri dichiarati possono essere utilizzati come normali variabili all’interno della procedura memorizzata. È possibile assegnare valori ai parametri ed eseguire istruzioni SQL basate su tali valori.

Esempio:

CREATE PROCEDURE UpdateEmployeeDepartment
    @EmployeeID INT,
    @NewDepartmentID INT
AS
BEGIN
    UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;
END;

In questa procedura memorizzata, vengono utilizzati due parametri di input @EmployeeID e @NewDepartmentID per aggiornare il reparto di un dipendente.

Esempio Pratico: Inserimento Dati con Parametri

Un esempio di come utilizzare i parametri per inserire dati:

Esempio:

CREATE PROCEDURE AddNewEmployee
    @EmployeeName NVARCHAR(50),
    @HireDate DATE,
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, HireDate, DepartmentID)
    VALUES (@EmployeeName, @HireDate, @DepartmentID);
END;

Questa procedura memorizzata inserisce le informazioni di un nuovo dipendente nella tabella Employees.

Tipi di Dati dei Parametri

I tipi di dati che possono essere utilizzati nei parametri delle procedure memorizzate sono diversi. Scegliere il tipo di dati appropriato consente una manipolazione dei dati efficiente e accurata. Qui spieghiamo i principali tipi di dati e come sceglierli.

Tipi di Dati di Base

Di seguito sono riportati i tipi di dati di base comunemente utilizzati nelle procedure memorizzate:

  • INT: Memorizza valori interi. Esempio: età, ID.
  • DECIMAL: Memorizza numeri con decimali. Esempio: prezzo, stipendio.
  • NVARCHAR: Memorizza stringhe di caratteri di lunghezza variabile. Esempio: nomi, indirizzi.
  • DATE: Memorizza date. Esempio: data di nascita, data di assunzione.

Come Scegliere i Tipi di Dati

Quando si sceglie il tipo di dati per i parametri, è importante considerare i seguenti aspetti:

Natura dei Dati

Scegli il tipo di dati in base alla natura dei dati. Ad esempio, per quantità o conteggi utilizza il tipo INT, per prezzi o percentuali utilizza il tipo DECIMAL, e per informazioni testuali utilizza il tipo NVARCHAR.

Efficienza dello Storage

La scelta del tipo di dati influisce anche sull’efficienza dello storage. Scegliendo la dimensione appropriata per il tipo di dati, è possibile minimizzare l’uso dello storage. Ad esempio, per stringhe brevi, specifica una lunghezza adeguata come NVARCHAR(50).

Precisione dei Dati

Per i dati numerici, considera la precisione e la scala necessarie quando scegli il tipo di dati. Ad esempio, per gli importi monetari, specifica DECIMAL(10, 2) per gestire numeri con due cifre decimali.

Esempio Pratico: Scelta del Tipo di Dati per i Parametri

Di seguito è riportato un esempio di come scegliere il tipo di dati appropriato in una procedura memorizzata che gestisce le informazioni sui dipendenti.

CREATE PROCEDURE AddEmployee
    @EmployeeName NVARCHAR(100),
    @BirthDate DATE,
    @Salary DECIMAL(10, 2),
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
    VALUES (@EmployeeName, @BirthDate, @Salary, @DepartmentID);
END;

In questo esempio, il nome del dipendente utilizza NVARCHAR(100), la data di nascita DATE, lo stipendio DECIMAL(10, 2) e l’ID del reparto INT.

Utilizzo dei Parametri per le Condizioni

Utilizzare i parametri nelle procedure memorizzate per gestire condizioni consente di creare query flessibili e potenti. Qui spieghiamo i metodi di base per gestire le condizioni e forniamo esempi pratici.

Condizioni di Base

È possibile utilizzare l’istruzione IF per eseguire operazioni diverse in base al valore di un parametro.

Esempio:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT,
    @IncludeSalary BIT
AS
BEGIN
    IF @IncludeSalary = 1
    BEGIN
        SELECT Name, BirthDate, Salary FROM Employees WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        SELECT Name, BirthDate FROM Employees WHERE EmployeeID = @EmployeeID;
    END
END;

In questa procedura memorizzata, il parametro @IncludeSalary determina se includere o meno le informazioni sullo stipendio.

Condizioni Complesse

Combinando più condizioni, è possibile implementare logiche più complesse.

Esempio:

CREATE PROCEDURE FilterEmployees
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10, 2) = NULL,
    @MaxSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    SELECT * FROM Employees
    WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
    AND (@MinSalary IS NULL OR Salary >= @MinSalary)
    AND (@MaxSalary IS NULL OR Salary <= @MaxSalary);
END;

In questo esempio, i dipendenti vengono filtrati in base ai parametri @DepartmentID, @MinSalary e @MaxSalary. Se un parametro è NULL, la condizione corrispondente viene ignorata.

Esempio Pratico: Aggiornamento Dati con Condizioni

Di seguito è riportato un esempio di come utilizzare i parametri per aggiornare i dati dei dipendenti con condizioni.

Esempio:

CREATE PROCEDURE UpdateEmployeeInfo
    @EmployeeID INT,
    @NewName NVARCHAR(100) = NULL,
    @NewDepartmentID INT = NULL,
    @NewSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    IF @NewName IS NOT NULL
    BEGIN
        UPDATE Employees SET Name = @NewName WHERE EmployeeID = @EmployeeID;
    END

    IF @NewDepartmentID IS NOT NULL
    BEGIN
        UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;
    END

    IF @NewSalary IS NOT NULL
    BEGIN
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
    END
END;

Questa procedura memorizzata aggiorna le informazioni sui dipendenti in base ai parametri @NewName, @NewDepartmentID e @NewSalary. I campi vengono aggiornati solo se i relativi parametri non sono NULL.

Utilizzo di Parametri Multipli

L’utilizzo di più parametri in una procedura memorizzata consente di eseguire query più flessibili e complesse. Qui spieghiamo come utilizzare correttamente i parametri multipli e le best practices da seguire.

Fondamenti dei Parametri Multipli

Per aggiungere più parametri a una procedura memorizzata, dichiara ciascun parametro separandoli con una virgola.

Sintassi di base:

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType,
    ...
AS
BEGIN
    -- Inserisci qui le istruzioni SQL
END;

Esempio:

CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT,
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID AND DepartmentID = @DepartmentID;
END;

In questo esempio, vengono utilizzati due parametri, @EmployeeID e @DepartmentID, per ottenere informazioni su un dipendente specifico.

Valori Predefiniti dei Parametri

Impostando valori predefiniti per i parametri, è possibile omettere il valore al momento della chiamata.

Esempio:

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT = NULL
AS
BEGIN
    IF @DepartmentID IS NULL
    BEGIN
        SELECT * FROM Employees;
    END
    ELSE
    BEGIN
        SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
    END
END;

In questa procedura memorizzata, se non viene specificato @DepartmentID, vengono recuperati tutti i dipendenti, mentre se viene specificato, vengono recuperati solo i dipendenti del reparto indicato.

Ordine e Specifica dei Parametri

Quando si richiama una procedura memorizzata, è importante prestare attenzione all’ordine dei parametri. Specificando il nome del parametro, è possibile eseguire la chiamata senza dipendere dall’ordine.

Esempio:

EXEC GetEmployeeInfo @EmployeeID = 1, @DepartmentID = 2;

In questa chiamata, specificando il nome dei parametri, è possibile passare i valori correttamente indipendentemente dall’ordine.

Esempio Pratico: Inserimento Dati con Parametri Multipli

Di seguito è riportato un esempio di come utilizzare più parametri per inserire nuove informazioni sui dipendenti.

Esempio:

CREATE PROCEDURE AddEmployee
    @EmployeeName NVARCHAR(100),
    @BirthDate DATE,
    @Salary DECIMAL(10, 2),
    @DepartmentID INT
AS
BEGIN
    INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
    VALUES (@EmployeeName, @BirthDate, @Salary, @DepartmentID);
END;

In questa procedura memorizzata, vengono utilizzati quattro parametri, @EmployeeName, @BirthDate, @Salary e @DepartmentID, per inserire nuove informazioni sui dipendenti.

Esempio Pratico: Generazione di SQL Dinamico

Generare SQL dinamico utilizzando i parametri consente di creare query flessibili e generiche. Tuttavia, quando si utilizza SQL dinamico, è importante prestare attenzione ai rischi di SQL injection e adottare le misure appropriate.

Sintassi di Base per SQL Dinamico

Per eseguire SQL dinamico, si utilizza EXEC o sp_executesql. In particolare, l’uso di sp_executesql consente di eseguire query parametrizzate, migliorando la sicurezza.

Esempio:

CREATE PROCEDURE SearchEmployees
    @SearchTerm NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM Employees WHERE Name LIKE @Term'

    EXEC sp_executesql @SQL, N'@Term NVARCHAR(100)', @Term = '%' + @SearchTerm + '%'
END;

In questa procedura memorizzata, il parametro @SearchTerm viene utilizzato per cercare record che corrispondono parzialmente al nome del dipendente.

Vantaggi e Attenzioni per SQL Dinamico

I vantaggi dell’utilizzo di SQL dinamico sono i seguenti:

  • Flessibilità: È possibile modificare dinamicamente la query al momento dell’esecuzione.
  • Riusabilità: È possibile riutilizzare la stessa query di base con condizioni diverse.

Punti di attenzione:

  • Rischio di SQL injection: Utilizzare sempre query parametrizzate per evitare il rischio di SQL injection.
  • Prestazioni: SQL dinamico potrebbe avere prestazioni inferiori rispetto a SQL statico.

Esempio Pratico: Generazione di SQL Dinamico con Condizioni Multiple

Di seguito è riportato un esempio di come generare SQL dinamico utilizzando più parametri per gestire condizioni multiple.

Esempio:

CREATE PROCEDURE FilterEmployees
    @Name NVARCHAR(100) = NULL,
    @MinSalary DECIMAL(10, 2) = NULL,
    @MaxSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM Employees WHERE 1=1'

    IF @Name IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Name LIKE @Name'
    END

    IF @MinSalary IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Salary >= @MinSalary'
    END

    IF @MaxSalary IS NOT NULL
    BEGIN
        SET @SQL = @SQL + ' AND Salary <= @MaxSalary'
    END

    EXEC sp_executesql @SQL,
        N'@Name NVARCHAR(100), @MinSalary DECIMAL(10, 2), @MaxSalary DECIMAL(10, 2)',
        @Name = '%' + @Name + '%',
        @MinSalary = @MinSalary,
        @MaxSalary = @MaxSalary
END;

In questa procedura memorizzata, i dipendenti vengono filtrati in base ai parametri @Name, @MinSalary e @MaxSalary. Ogni condizione viene aggiunta dinamicamente.

Validazione dei Parametri e Gestione degli Errori

Quando si utilizzano parametri nelle procedure memorizzate, è importante validare i valori dei parametri e gestire correttamente gli errori. Questo assicura la coerenza e l’affidabilità dei dati.

Validazione dei Parametri

Validare i valori dei parametri all’interno della procedura memorizzata aiuta a prevenire dati errati o errori imprevisti.

Esempio:

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    -- Validazione dei parametri
    IF @EmployeeID <= 0
    BEGIN
        RAISERROR('EmployeeID must be greater than 0', 16, 1)
        RETURN
    END

    IF @NewSalary < 0
    BEGIN
        RAISERROR('Salary cannot be negative', 16, 1)
        RETURN
    END

    -- Aggiornamento
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
END;

In questa procedura memorizzata, si valida che @EmployeeID sia un numero positivo e che @NewSalary non sia un valore negativo. Se le condizioni non sono soddisfatte, viene generato un errore e l’esecuzione viene interrotta.

Gestione degli Errori

È importante gestire correttamente i potenziali errori all’interno delle procedure memorizzate. Un metodo comune è utilizzare i blocchi TRY...CATCH.

Esempio:

CREATE PROCEDURE TransferEmployee
    @EmployeeID INT,
    @NewDepartmentID INT
AS
BEGIN
    BEGIN TRY
        -- Inizio della transazione
        BEGIN TRANSACTION

        -- Aggiornamento del reparto
        UPDATE Employees SET DepartmentID = @NewDepartmentID WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Ottenimento del messaggio di errore
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Generazione dell'errore
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

In questa procedura memorizzata, viene eseguito un aggiornamento del reparto all’interno di una transazione. Se si verifica un errore, la transazione viene annullata e viene visualizzato un messaggio di errore.

Esempio Pratico: Combinare Validazione dei Parametri e Gestione degli Errori

Di seguito è riportato un esempio di come combinare la validazione dei parametri e la gestione degli errori per creare una procedura memorizzata più robusta.

Esempio:

CREATE PROCEDURE PromoteEmployee
    @EmployeeID INT,
    @NewTitle NVARCHAR(100),
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Validazione dei parametri
        IF @EmployeeID <= 0
        BEGIN
            RAISERROR('EmployeeID must be greater than 0', 16, 1)
            RETURN
        END

        IF @NewSalary < 0
        BEGIN
            RAISERROR('Salary cannot be negative', 16, 1)
            RETURN
        END

        -- Inizio della transazione
        BEGIN TRANSACTION

        -- Promozione del dipendente
        UPDATE Employees SET Title = @NewTitle, Salary = @NewSalary WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Ottenimento del messaggio di errore
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Generazione dell'errore
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

In questa procedura memorizzata, si validano i valori del dipendente e del nuovo stipendio, e si esegue la promozione all’interno di una transazione. Se si verifica un errore, la transazione viene annullata e viene visualizzato un messaggio di errore.

Esempio Avanzato: Batch Processing con Procedure Memorizzate

Le procedure memorizzate sono uno strumento potente per eseguire batch processing in modo efficiente. Quando si gestiscono grandi quantità di dati, l’utilizzo delle procedure memorizzate consente di migliorare le prestazioni e riutilizzare il codice.

Concetti di Base del Batch Processing

Il batch processing si riferisce all’elaborazione di grandi quantità di dati in blocco, eseguendo operazioni come inserimento, aggiornamento o eliminazione di dati in una volta sola. Questo permette di manipolare i dati in modo più efficiente rispetto all’elaborazione individuale.

Vantaggi del Batch Processing

  • Miglioramento delle Prestazioni: Consente di elaborare grandi quantità di dati in una sola volta, risultando più veloce rispetto all’elaborazione individuale.
  • Coerenza: Utilizzando le transazioni, è possibile mantenere la coerenza dei dati.
  • Riusabilità: È possibile eseguire lo stesso processo più volte, aumentando la riusabilità del codice.

Esempio di Batch Processing: Aggiornamento di Stipendi

Di seguito è riportata una procedura memorizzata che aggiorna in blocco gli stipendi dei dipendenti in base a determinate condizioni.

Esempio:

CREATE PROCEDURE UpdateSalariesBatch
    @DepartmentID INT,
    @SalaryIncrease DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Inizio della transazione
        BEGIN TRANSACTION

        -- Aggiornamento degli stipendi per il reparto specificato
        UPDATE Employees
        SET Salary = Salary + @SalaryIncrease
        WHERE DepartmentID = @DepartmentID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Ottenimento del messaggio di errore
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Generazione dell'errore
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

In questa procedura memorizzata, gli stipendi di tutti i dipendenti di un determinato reparto vengono aggiornati in blocco. Viene utilizzata una transazione per garantire la coerenza, e in caso di errore viene eseguito il rollback.

Esempio di Batch Processing: Inserimento di Grandi Quantità di Dati

Di seguito è riportata una procedura memorizzata che legge i dati da un’altra tabella e inserisce una grande quantità di record in blocco.

Esempio:

CREATE PROCEDURE InsertNewEmployeesBatch
AS
BEGIN
    BEGIN TRY
        -- Inizio della transazione
        BEGIN TRANSACTION

        -- Inserimento di nuovi dati sui dipendenti in blocco
        INSERT INTO Employees (Name, BirthDate, Salary, DepartmentID)
        SELECT Name, BirthDate, Salary, DepartmentID
        FROM NewEmployees;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Ottenimento del messaggio di errore
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Generazione dell'errore
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

In questa procedura memorizzata, i dati vengono letti dalla tabella NewEmployees e inseriti in blocco nella tabella Employees. Viene utilizzata una transazione per garantire la coerenza e, in caso di errore, viene eseguito il rollback.

Esercizi

Per approfondire l’apprendimento e sviluppare competenze pratiche, forniamo esercizi che ti aiuteranno a comprendere meglio l’uso dei parametri nelle procedure memorizzate e le applicazioni del batch processing.

Esercizio 1: Creazione di una Procedura Memorizzata di Base

Crea una procedura memorizzata di base in base ai seguenti requisiti:

  • Ricevi EmployeeID come parametro di input e recupera le informazioni dettagliate su quel dipendente.
  • Le informazioni sul dipendente devono includere Name, BirthDate e DepartmentID.

Esempio di Risoluzione

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT Name, BirthDate, DepartmentID
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Esercizio 2: Creazione di una Procedura Memorizzata con Condizioni

Crea una procedura memorizzata con condizioni in base ai seguenti requisiti:

  • Ricevi DepartmentID e MinSalary come parametri di input e filtra i dipendenti in base alle condizioni specificate.
  • Se DepartmentID è NULL, cerca tra tutti i dipendenti.
  • Se MinSalary è NULL, non applicare alcuna condizione relativa allo stipendio.

Esempio di Risoluzione

CREATE PROCEDURE FilterEmployees
    @DepartmentID INT = NULL,
    @MinSalary DECIMAL(10, 2) = NULL
AS
BEGIN
    SELECT * FROM Employees
    WHERE (@DepartmentID IS NULL OR DepartmentID = @DepartmentID)
    AND (@MinSalary IS NULL OR Salary >= @MinSalary);
END;

Esercizio 3: Creazione di una Procedura Memorizzata con Gestione degli Errori

Crea una procedura memorizzata con gestione degli errori in base ai seguenti requisiti:

  • Ricevi EmployeeID e NewSalary come parametri di input e aggiorna lo stipendio del dipendente.
  • Genera un errore se EmployeeID è inferiore o uguale a 0 o se NewSalary è un valore negativo.
  • Utilizza una transazione per garantire la coerenza.

Esempio di Risoluzione

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        -- Validazione dei parametri
        IF @EmployeeID <= 0
        BEGIN
            RAISERROR('EmployeeID must be greater than 0', 16, 1)
            RETURN
        END

        IF @NewSalary < 0
        BEGIN
            RAISERROR('Salary cannot be negative', 16, 1)
            RETURN
        END

        -- Inizio della transazione
        BEGIN TRANSACTION

        -- Aggiornamento dello stipendio del dipendente
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;

        -- Commit
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Rollback
        ROLLBACK TRANSACTION

        -- Ottenimento del messaggio di errore
        DECLARE @ErrorMessage NVARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()

        -- Generazione dell'errore
        RAISERROR(@ErrorMessage, 16, 1)
    END CATCH
END;

Conclusione

L’utilizzo dei parametri nelle procedure SQL migliora significativamente l’efficienza e la flessibilità nella manipolazione dei dati. In questo articolo abbiamo coperto una vasta gamma di argomenti, dai tipi di parametri, alla dichiarazione, agli esempi pratici, alla gestione degli errori e alle applicazioni del batch processing. Utilizzando queste conoscenze, potrai realizzare operazioni di database più complesse ed efficienti. Utilizzare correttamente i parametri consente di creare codice SQL sicuro e facile da mantenere.

SQL
Database Elaborazione in Batch Gestione degli errori Parametri Parametri di Input Parametri di Output Procedura Memorizzata SQL
Indice