SET @local_variable (Transact-SQL)

Articolo
12/15/2008

Imposta la variabile locale specificata, creata in precedenza tramite l'istruzione DECLARE **@**local_variable, sul valore specificato.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

SET 
{ @local_variable 
    [:: property_name | field_name ] = expression | udt_name { . | :: } method_name(argument [ ,...n ] ) 
}
| 
{ @cursor_variable = 
    { @cursor_variable | cursor_name 
    | { CURSOR [ FORWARD_ONLY | SCROLL ] 
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
        [ TYPE_WARNING ] 
    FOR select_statement 
        [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] 
      } 
    }
}

Argomenti

**@**local_variable

Nome di una variabile di qualsiasi tipo, ad eccezione di cursor, text, ntext, image o table. I nomi delle variabili devono iniziare con un simbolo di chiocciola (@) e devono essere conformi alle regole per gli identificatori.

property_name

Proprietà di un tipo definito dall'utente.

field_name

Campo pubblico di un tipo definito dall'utente.

udt_name

Nome di un tipo CLR (Common Language Runtime) definito dall'utente.

{ . | :: }

Specifica un metodo di un tipo CRL definito dall'utente. Per un metodo di istanza (non statico), utilizzare un punto (.). Per un metodo statico, utilizzare una coppia di due punti (::). Per richiamare un metodo, una proprietà o un campo di un tipo CLR definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per il tipo.

method_name**(**argument [ ,... n ] )

Metodo di un tipo definito dall'utente che accetta uno o più argomenti per modificare lo stato di un'istanza di un tipo. I metodi statici devono essere pubblici.

expression

Qualsiasi espressione valida.

cursor_variable

Nome di una variabile di cursore. Se in precedenza la variabile di cursore di destinazione faceva riferimento a un cursore diverso, il riferimento precedente viene rimosso.

cursor_name

Nome di un cursore dichiarato tramite l'istruzione DECLARE CURSOR.

CURSOR

Specifica che l'istruzione SET contiene una dichiarazione di cursore.

SCROLL

Specifica che il cursore supporta tutte le opzioni di recupero, ovvero FIRST, LAST, NEXT, PRIOR, RELATIVE e ABSOLUTE. L'opzione SCROLL non può essere utilizzata quando viene specificata l'opzione FAST_FORWARD.

FORWARD_ONLY

Specifica che il cursore supporta solo l'opzione di recupero FETCH NEXT. È possibile recuperare il cursore solo in una direzione, ovvero dalla prima riga all'ultima. Se si specifica l'opzione FORWARD_ONLY senza la parola chiave STATIC, KEYSET o DYNAMIC, il cursore viene implementato come DYNAMIC. Se vengono omesse sia l'opzione FORWARD_ONLY che l'opzione SCROLL, per impostazione predefinita viene utilizzata l'opzione FORWARD_ONLY, a meno che non sia stata specificata la parola chiave STATIC, KEYSET o DYNAMIC. Per i cursori di tipo STATIC, KEYSET e DYNAMIC, l'opzione predefinita è SCROLL.

[!NOTA] In SQL Server 2000 le opzioni di cursore FAST_FORWARD e FORWARD_ONLY si escludono a vicenda. Se viene specificata una di queste due opzioni, l'altra non può essere impostata e quando si tenta di utilizzarla viene generato un errore. In SQL Server 2005 è possibile utilizzare entrambe le parole chiave in una stessa istruzione DECLARE CURSOR.

STATIC

Definisce un cursore che crea una copia temporanea dei dati in esso utilizzati. Le richieste inviate al cursore vengono soddisfatte tramite questa tabella temporanea di tempdb. Le modifiche apportate alle tabelle di base non vengono pertanto riportate nei dati restituiti dalle operazioni di recupero eseguite in questo cursore, che non supporta operazioni di modifica.

KEYSET

Specifica che all'apertura del cursore l'appartenenza e l'ordine delle righe nel cursore sono fissi. Il set di chiavi che identifica le righe in modo univoco è incluso nella tabella keyset di tempdb. Le modifiche di valori non chiave delle tabelle di base che sono state apportate dal proprietario del cursore o di cui è stato eseguito il commit da altri utenti sono visibili quando il proprietario scorre il cursore. Gli inserimenti eseguiti da altri utenti non sono visibili e non è possibile eseguire inserimenti tramite un cursore Transact-SQL del server.

Se viene eliminata una riga, i tentativi di recupero di tale riga provocano la restituzione del valore -2 per la funzione @@FETCH_STATUS. Le operazioni di aggiornamento di valori di chiave dall'esterno del cursore sono simili a un'operazione di eliminazione della riga precedente seguita da un'operazione di inserimento della nuova riga. La riga contenente i nuovi valori non è visibile e i tentativi di recupero della riga contenente i valori precedenti provocano la restituzione del valore -2 per la funzione @@FETCH_STATUS. I nuovi valori sono visibili se l'aggiornamento viene eseguito tramite il cursore con l'aggiunta della clausola WHERE CURRENT OF.

DYNAMIC

Definisce un cursore che visualizza nel set di risultati tutte le modifiche apportate ai dati delle righe quando il proprietario scorre il cursore. I valori dei dati, l'ordine e l'appartenenza delle righe possono cambiare a ogni operazione di recupero. I cursori dinamici non supportano le opzioni di recupero assoluto e relativo.

FAST_FORWARD

Specifica un cursore FORWARD_ONLY e READ_ONLY con le ottimizzazioni attivate. L'opzione FAST_FORWARD non può essere utilizzata quando viene specificata l'opzione SCROLL.

[!NOTA] In SQL Server 2000 le opzioni di cursore FAST_FORWARD e FORWARD_ONLY si escludono a vicenda. Se viene specificata una di queste due opzioni, l'altra non può essere impostata e quando si tenta di utilizzarla viene generato un errore. In SQL Server 2005 è possibile utilizzare entrambe le parole chiave in una stessa istruzione DECLARE CURSOR.

READ_ONLY

SCROLL LOCKS

Specifica che gli aggiornamenti o le eliminazioni posizionate eseguite tramite il cursore avranno sempre esito positivo. SQL Server blocca le righe a mano a mano che vengono lette nel cursore in modo da garantirne la disponibilità per le successive operazioni di modifica. L'opzione SCROLL_LOCKS non può essere utilizzata quando viene specificata l'opzione FAST_FORWARD.

OPTIMISTIC

Specifica che gli aggiornamenti o le eliminazioni posizionate eseguite tramite il cursore non avranno esito positivo se le riga ha subito un aggiornamento dopo la lettura nel cursore. SQL Server non blocca le righe a mano a mano che vengono lette nel cursore. Per determinare se la riga è stata modificata dopo la lettura nel cursore, vengono utilizzati confronti tra i valori della colonna di tipo timestamp oppure un valore di checksum se la tabella non include una colonna di tipo timestamp. Se la riga è stata modificata, i tentativi di eseguire un aggiornamento o un'eliminazione posizionata hanno esito negativo. L'opzione OPTIMISTIC non può essere utilizzata quando viene specificata l'opzione FAST_FORWARD.

TYPE_WARNING

Specifica che deve essere inviato un messaggio di avviso al client quando il cursore viene convertito in modo implicito dal tipo richiesto in un altro tipo.

FOR select_statement

Istruzione SELECT standard che definisce il set di risultati del cursore. Le parole chiave COMPUTE, COMPUTE BY, FOR BROWSE e INTO non possono essere utilizzate nell'argomento select_statement di una dichiarazione di cursore.

Se viene specificata la parola chiave DISTINCT, UNION, GROUP BY o HAVING oppure se select_list include un'espressione di aggregazione, il cursore viene creato come STATIC.

Se nessuna tabella sottostante include un indice univoco e viene richiesto un cursore SQL-92 SCROLL o un cursore Transact-SQL KEYSET, il cursore sarà sempre di tipo STATIC.

Se select_statement include una clausola ORDER BY con colonne che non sono identificatori di riga univoci, un cursore DYNAMIC viene convertito in un cursore KEYSET o, se non è possibile aprire un cursore KEYSET, in un cursore STATIC. Questa conversione ha luogo anche per i cursori definiti con la sintassi SQL-92 ma senza la parola chiave STATIC.

READ ONLY

Impedisce l'esecuzione di aggiornamenti tramite il cursore. Non è possibile fare riferimento al cursore in una clausola WHERE CURRENT OF di un'istruzione UPDATE o DELETE. Questa opzione è prioritaria rispetto alla funzionalità di aggiornamento predefinita di un cursore. Questa parola chiave si differenzia dalla parola chiave READ_ONLY descritta in precedenza in quanto tra READ e ONLY esiste uno spazio anziché un carattere di sottolineatura.

UPDATE [OF column_name[ ,... n ] ]

Definisce le colonne aggiornabili nel cursore. Se viene specificato OF column_name [,...n], è possibile apportare modifiche solo nelle colonne elencate. Se non viene specificata alcuna colonna, è possibile aggiornare tutte le colonne, a meno che il cursore non sia stato definito come READ_ONLY.

Osservazioni

Dopo la dichiarazione, le variabili vengono inizializzate sul valore NULL. Per assegnare un valore diverso da NULL a una variabile dichiarata, utilizzare l'istruzione SET. L'istruzione SET che assegna un valore alla variabile restituisce un solo valore. Per inizializzare più variabili, utilizzare un'istruzione SET separata per ogni variabile locale.

Le variabili possono essere utilizzate solo nelle espressioni e non in sostituzione di parole chiave o nomi di oggetto. Per creare istruzioni Transact-SQL dinamiche, utilizzare EXECUTE.

Le regole di sintassi relative a SET **@**cursor_variable non prevedono le parole chiave LOCAL e GLOBAL. Quando viene utilizzata la sintassi SET **@**cursor_variable = CURSOR..., viene creato un cursore GLOBAL o LOCAL a seconda dell'impostazione dell'opzione del database default to local cursor.

Le variabili di cursore sono sempre locali, anche quando fanno riferimento a un cursore globale. Quando una variabile di cursore fa riferimento a un cursore globale, esistono sia un riferimento al cursore locale che un riferimento al cursore globale. Per ulteriori informazioni, vedere l'esempio C.

Per ulteriori informazioni, vedere DECLARE CURSOR (Transact-SQL).

Autorizzazioni

È richiesta l'appartenenza al ruolo public. La sintassi SET **@**local_variable può essere utilizzata da qualsiasi utente.

Esempi

A. Visualizzazione del valore di una variabile inizializzata tramite l'istruzione SET

Nell'esempio seguente viene creata la variabile @myvar, viene immesso un valore stringa nella variabile e quindi viene visualizzato il valore della variabile @myvar .

DECLARE @myvar char(20);
SET @myvar = 'This is a test';
SELECT @myvar;
GO

B. Utilizzo in un'istruzione SELECT di una variabile locale a cui viene assegnato un valore tramite l'istruzione SET

Nell'esempio seguente viene creata una variabile locale denominata @state che viene quindi utilizzata in un'istruzione SELECT per individuare i nomi e i cognomi di tutti i dipendenti che risiedono nello stato dell'Oregon.

USE AdventureWorks;
GO
DECLARE @state char(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;

C. Utilizzo dell'istruzione SET con un cursore globale

Nell'esempio seguente viene creata una variabile locale e quindi viene impostata la variabile di cursore sul nome del cursore globale.

DECLARE my_cursor CURSOR GLOBAL 
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ; 
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.
DEALLOCATE my_cursor; 
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.

D. Definizione di un cursore tramite l'istruzione SET

Nell'esempio seguente viene utilizzata l'istruzione SET per definire un cursore.

DECLARE @CursorVar CURSOR;

SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks.HumanResources.vEmployee
WHERE LastName like 'B%';

OPEN @CursorVar;

FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @CursorVar
END;

CLOSE @CursorVar;
DEALLOCATE @CursorVar;

E. Assegnazione di un valore tramite una query

Nell'esempio seguente viene utilizzata una query per assegnare un valore a una variabile.

USE AdventureWorks;
GO
DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;

F. Assegnazione di un valore a una variabile con tipo definito dall'utente tramite la modifica di una proprietà del tipo

Nell'esempio seguente viene impostato un valore per il tipo definito dall'utente Point tramite la modifica del valore della proprietà X del tipo.

DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO

G. Assegnazione di un valore a una variabile con tipo definito dall'utente tramite la chiamata di un metodo del tipo

Nell'esempio seguente viene impostato un valore per il tipo definito dall'utente point tramite la chiamata del metodo SetXY del tipo.

DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);

Vedere anche