Variabili (Transact-SQL)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)Endpoint di analisi SQL in Microsoft FabricWarehouse in Microsoft Fabric

Una variabile Transact-SQL locale è un oggetto che può contenere un solo valore di dati di un tipo specifico. Le variabili vengono in genere utilizzate in batch e script per gli scopi seguenti:

  • Come contatore per contare il numero di volte in cui viene eseguito un ciclo o per controllare il numero di esecuzioni del ciclo.
  • Archiviare un valore di dati che deve essere testato da un'istruzione per il controllo di flusso.
  • Salvare un valore di dati che deve essere restituito dal codice restituito di una stored procedure o dal valore restituito da una funzione.

Osservazioni:

Il nome di alcune funzioni di sistema Transact-SQL inizia con due simboli di chiocciola (@@). Anche se nelle versioni precedenti di SQL Server, le @@ funzioni vengono definite variabili globali, @@ le funzioni non sono variabili e non hanno gli stessi comportamenti delle variabili. Le @@ funzioni sono funzioni di sistema e l'utilizzo della sintassi segue le regole per le funzioni.

Non è possibile usare le variabili in una vista.

Le modifiche apportate alle variabili non sono interessate dal rollback di una transazione.

Dichiarare una variabile Transact-SQL

L'istruzione DECLARE inizializza una variabile Transact-SQL in base a:

  • Assegnazione di un nome. Il nome deve iniziare con il carattere chiocciola (@).

  • Assegnazione di un tipo di dati definito dall'utente o di sistema, nonché la lunghezza del tipo di dati. Alle variabili numeriche vengono inoltre assegnate precisione e scala. Per le variabili di tipo XML, è possibile assegnare una raccolta di schemi facoltativa.

  • Impostazione del valore su NULL.

Ad esempio, l'istruzione seguente DECLARE crea una variabile locale denominata @mycounter con un tipo di dati int . Per impostazione predefinita, il valore per questa variabile è NULL.

DECLARE @MyCounter INT;

Per dichiarare più variabili locali, è necessario inserire una virgola dopo la prima variabile locale e quindi specificare il nome della variabile locale successiva con il tipo di dati corrispondente.

Ad esempio, l'istruzione seguente DECLARE crea tre variabili locali denominate @LastName, @FirstName e @StateProvincee inizializza ognuna in NULL:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

L'ambito di una variabile definisce l'intervallo di istruzioni Transact-SQL in cui è possibile fare riferimento alla variabile. L'ambito di una variabile dura dal punto in cui viene dichiarato fino alla fine del batch o della stored procedure in cui viene dichiarata. Ad esempio, lo script seguente genera un errore di sintassi perché la variabile viene dichiarata in un batch (separato dalla GO parola chiave ) e a cui viene fatto riferimento in un altro:

USE AdventureWorks2022;
GO

DECLARE @MyVariable INT;

SET @MyVariable = 1;
GO

SELECT BusinessEntityID,
    NationalIDNumber,
    JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

Le variabili hanno un ambito locale e sono visibili solo all'interno del batch o della procedura in cui sono definite. Nell'esempio seguente l'ambito annidato creato per l'esecuzione di sp_executesql non ha accesso alla variabile dichiarata nell'ambito superiore e restituisce ed errore.

DECLARE @MyVariable INT;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error

Impostare un valore in una variabile Transact-SQL

Quando una variabile viene dichiarata per la prima volta, il relativo valore viene impostato su NULL. Per assegnare un valore a una variabile, usare l'istruzione SET . È consigliabile adottare sempre questo metodo. Una variabile può anche avere un valore assegnato facendo riferimento nell'elenco di selezione di un'istruzione SELECT .

Se si desidera utilizzare l'istruzione SET per assegnare un valore a una variabile, specificare il nome della variabile e il valore da assegnarvi. È consigliabile adottare sempre questo metodo. Nel batch seguente vengono dichiarate due variabili, alle quali vengono quindi assegnati valori, utilizzati successivamente nella clausola WHERE di un'istruzione SELECT:

USE AdventureWorks2022;
GO

-- Declare two variables.
DECLARE @FirstNameVariable NVARCHAR(50),
    @PostalCodeVariable NVARCHAR(15);

-- Set their values.
SET @FirstNameVariable = N'Amy';
SET @PostalCodeVariable = N'BA5 3HX';

-- Use them in the WHERE clause of a SELECT statement.
SELECT LastName,
    FirstName,
    JobTitle,
    City,
    StateProvinceName,
    CountryRegionName
FROM HumanResources.vEmployee
WHERE FirstName = @FirstNameVariable
    OR PostalCode = @PostalCodeVariable;
GO

Quando si assegna un valore a una variabile tramite un riferimento in un elenco di selezione, Se viene fatto riferimento a una variabile in un elenco di selezione, deve essere assegnato un valore scalare oppure l'istruzione SELECT deve restituire solo una riga. Ad esempio:

USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;

SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;
GO

Avviso

Se in una singola SELECT istruzione sono presenti più clausole di assegnazione, SQL Server non garantisce l'ordine di valutazione delle espressioni. Gli effetti sono visibili solo se sono presenti riferimenti tra le assegnazioni.

Se un'istruzione SELECT restituisce più righe e la variabile fa riferimento a un'espressione non scalare, la variabile viene impostata sul valore restituito per l'espressione nell'ultima riga del set di risultati. Ad esempio, nel batch @EmpIDVariable seguente viene impostato sul BusinessEntityID valore dell'ultima riga restituita, ovvero 1:

USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;

SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;

SELECT @EmpIDVariable;
GO

Esempio

Lo script seguente crea e popola con 26 righe una piccola tabella di prova. Lo script utilizza una variabile per eseguire tre operazioni:

  • Controllare il numero di righe inserite verificando il numero di esecuzioni del ciclo.
  • Specificare il valore inserito nella colonna integer.
  • Funzionare come parte dell'espressione che genera le lettere da inserire nella colonna di tipo carattere.
-- Create the table.
CREATE TABLE TestTable (cola INT, colb CHAR(3));
GO

SET NOCOUNT ON;
GO

-- Declare the variable to be used.
DECLARE @MyCounter INT;

-- Initialize the variable.
SET @MyCounter = 0;

-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
    -- Insert a row into the table.
    INSERT INTO TestTable
    VALUES
        -- Use the variable to provide the integer value
        -- for cola. Also use it to generate a unique letter
        -- for each row. Use the ASCII function to get the
        -- integer value of 'a'. Add @MyCounter. Use CHAR to
        -- convert the sum back to the character @MyCounter
        -- characters after 'a'.
        (
        @MyCounter,
        CHAR((@MyCounter + ASCII('a')))
        );

    -- Increment the variable to count this iteration
    -- of the loop.
    SET @MyCounter = @MyCounter + 1;
END;
GO

SET NOCOUNT OFF;
GO

-- View the data.
SELECT cola, colb FROM TestTable;
GO

DROP TABLE TestTable;
GO

Contenuto correlato