Utilizzo dell’istruzione WHILE in Stored Procedure

Dopo aver letto questo articolo capirai le basi dell’utilizzo dell’istruzione WHILE per scrivere un ciclo all’interno di una stored procedure.

Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e sui database di esempio AdventureWorks e WideWorldImporters. È possibile iniziare a utilizzare questi strumenti gratuiti con la mia guida Per iniziare a utilizzare SQL Server.

WHILE, BREAK, and CONTINUE

L’istruzione WHILE viene utilizzata per eseguire ripetutamente un blocco di istruzioni SQL. Il blocco viene eseguito ripetutamente se la condizione dell’istruzione WHILE è vera.

Il formato generale delle istruzioni WHILE è:

WHILE conditionBEGIN -- code block run when condition is TRUEEND

Se ti ritrovi a ripetere le dichiarazioni, specialmente quelle che rientrano in uno schema, allora, c’è la possibilità che tu possa usare un’istruzione WHILE per salvare un po ‘ di digitazione e rendere il tuo programma più divertente da scrivere!

Ad esempio, supponiamo che sia necessario creare una tabella temporanea contenente la data di inizio di ogni settimana. Certo, potremmo scrivere 52 singole dichiarazioni di INSERIMENTO, ma è noioso!

INSERT INTO @myTable VALUES (0, 12/31/2017)INSERT INTO @myTable VALUES (1, 01/07/2018)INSERT INTO @myTable VALUES (2, 01/14/2018)…INSERT INTO @myTable VALUES (52, 12/30/2018 )

Invece usa il comando WHILE per scorrere le 52 settimane, inserendo la data di inizio di ogni settimana nella tabella. Puoi vedere come lo facciamo nel seguente esempio.

--Setup VariablesDECLARE @myTable TABLE(WeekNumber int, DateStarting smalldatetime)DECLARE @n int = 0DECLARE @firstWeek smalldatetime = '12/31/2017'--Loop Through weeksWHILE @n <= 52BEGIN INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek)); SELECT @n = @n + 1END--Show ResultsSELECT WeekNumber, DateStartingFROM @myTable

Ciò che rende questo segno di spunta è la funzione DATEADD. Si noti che DATEADD (wk, @n, @firstWeek) aggiunge il lavoro di una settimana di giorni alla nostra data @firstWeek. Nella prima iterazione aggiunge 0 settimane. Nella seconda iterazione, @n = 1, quindi aggiunge 1 settimana e così via.

Ecco alcuni altri punti:

• L’istruzione WHILE verifica la variabile @n. Se è <= 52, il blocco del programma (verde), può essere eseguito.
• Ogni volta che il blocco viene eseguito, la variabile @n viene incrementata di uno. Questo è importante. Se ciò non accadesse, il valore non sarebbe mai maggiore di 52 e il nostro programma eseguirebbe il blocco di codice, avanti e indietro, senza fine. Questo è chiamato un ciclo infinito.
• Rinforzo a #2 sopra! È importante che il tuo ciclo abbia una condizione finale. Nel nostro caso ci assicuriamo che la variabile @n sia incrementata e che alla fine sarà maggiore di 52.
• Usiamo il valore @n per “guidare” il valore della data. Aggiungendo settimane alla nostra data di base, @ firstWeek, possiamo calcolare le date successive della settimana di inizio.

Ecco i risultati:

Come puoi vedere, da questo semplice ciclo, siamo stati in grado di creare alcuni dati interessanti, ed è stato più divertente fare wo usando le funzioni date, piuttosto che “codificare” l’istruzione. L’uso dei loop è molto potente.

Utilizzo di BREAK per cortocircuitare un Loop

L’istruzione BREAK viene utilizzata per uscire forzatamente dal blocco di codice di un’istruzione WHILE.

Nel seguente esempio abbiamo modificato l’istruzione WHILE. Ora utilizza l’istruzione BREAK, che è colorata in verde.

Ci sono due modifiche:

1. La condizione dell’istruzione WHILE viene sempre valutata come TRUE, poiché @n è sempre maggiore o uguale a zero.
2. L’istruzione IF verifica la condizione finale e, se TRUE, esegue l’INTERRUZIONE.

DECLARE @myTable TABLE(WeekNumber int, DateStarting smalldatetime)DECLARE @n int = 0DECLARE @firstWeek smalldatetime = '12/31/2017'WHILE @n > -1BEGIN INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek)); SELECT @n = @n + 1 IF @n > 52 BREAKEND

L’istruzione BREAK è utile per terminare l’esecuzione. Se le istruzioni WHILE sono nidificate, il ciclo più interno viene chiuso.

Utilizzando CONTINUA per reimpostare un ciclo

L’istruzione CONTINUA riavvia il blocco di codice di un’istruzione WHILE. Tutte le istruzioni trovate dopo il CONTINUE non vengono eseguite.

Ad esempio, nel seguito, l’istruzione PRINT non viene mai eseguita.

DECLARE @myTable TABLE(WeekNumber int, DateStarting smalldatetime)DECLARE @n int = 0DECLARE @firstWeek smalldatetime = '12/31/2017'WHILE @n > -1BEGIN INSERT INTO @myTable VALUES (@n, DATEADD(wk,@n,@firstWeek)); SELECT @n = @n + 1 IF @n > 52 BREAK ELSE CONTINUE PRINT ‘I Never get executed’END

Il motivo è o l’istruzione BREAK forza un’uscita, o quando viene eseguito il CONTINUE, il ciclo viene ripristinato all’inizio del blocco.