Guida introduttiva alle stored Procedure in SQL Server
Di: Greg Robidoux | Updated: 2020-07-30 | Comments (15) | Related: More> Stored Procedure
Problema
Ho utilizzato SQL Server per qualche tempo, ma tutto il codice che viene emessocontro il database è incorporato nel codice dell’applicazione. So che è possibile creareprocedure memorizzate, ma non sono esattamente sicuro di dove iniziare o di cosa ho bisogno per implementare le stored procedure.,
Soluzione
Le stored procedure non sono altro che un batch di istruzioni T-SQL memorizzate nel database. Invece di dover emettere più istruzioni dall’applicazione, è possibile emettere un comando per chiamare la stored procedure per eseguire un lavoro anziché una sola istruzione. Inoltre, poiché il codice è memorizzato nel database, è possibile emettere lo stesso set di codice più e più volteanche da diverse applicazioni o da una finestra di query. Per iniziare, il resto di questo suggerimento esamina somesample stored procedure e come è possibile iniziare e costruire su di loro.,
Gli esempi seguenti mostrano quanto sia semplice creare stored procedure. Allof questi esempi usano il database di theAdventureWorks, ma theseexamples dovrebbe essere abbastanza straightforwardthat lei può applicare questi concetti ai Suoi propri database e applicazioni.
Esempio 1 – semplice stored procedure
Questo primo esempio crea una semplice stored procedure che ottiene il record TOP 1 dalla persona.Tabella di contatto.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
Dopo aver creato quanto sopra, utilizzare il comando seguente per eseguire questa storedprocedure.,
EXEC uspGetContact
Questo è il risultato di questa prima query.
Esempio 2 – stored procedure con un parametro
Questo esempio successivo è una modifica del primo esempio, ma questa volta aggiungendoun parametro che viene passato nella procedura per selezionare dinamicamente i record.Invece di usare CREATE PROCEDURE stiamo usando ALTER PROCEDURE per modificare la procedureche abbiamo creato nell’esempio 1 invece di rilasciarlo prima e poi ricrearlo.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
Sotto mostra due modi diversi la stored procedure può essere eseguito. Il primo examplejust passa il valore del parametro che vogliamo usare e il secondo esempio include anche il nome del parametro insieme al valore. È possibile eseguire la stored procedure con entrambiuno di questi comandi.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Questo è il risultato di questa prima query.,
Esempio 3 – stored procedure con un parametro e un parametro di output
In questo esempio abbiamo sia un parametro di input che un parametro di OUTPUT.Il parametro output verrà utilizzato per restituire il ContactID che stiamo cercando nella stored procedure. Questo parametro di output verrà quindi utilizzato per selezionare le persone ContactID, FirstName e LastName insieme a qualsiasi record di indirizzo per thisperson.,
Ancora una volta stiamo alterando la stored procedure uspGetContact e poi in secondo luogo stiamo eseguendo il prossimo set di codice che esegue la procedura uspGetContact e quindi in base al valore di ritorno che ottiene, interrogherà anche per il nome delle persone e addressinfo.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
Dopo che la stored procedure è stata modificata, eseguire il seguente blocco di codice. Thiswill eseguire la stored procedure di cui sopra e se il ContactID ha un valore che willalso restituire la persona e indirizzo informazioni.
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
Questo è il risultato.,
Esempio 4 – stored procedure utilizzando l’istruzione RAISERROR
In questo esempio stiamo combinando i due passaggi nell’esempio 3 in una stored procedure.Il primo passo è quello di ottenere il ContactID e poi la seconda parte della procedurewill ricerca le persone nome e indirizzo informazioni. Abbiamo anche aggiunto il codice per utilizzare l’istruzione theRAISERROR per restituire un errore se non vengono trovati record.
Questo viene quindi eseguito due volte per mostrare come appare quando i dati vengono trovati e quando non vengono trovati dati., L’istruzione RAISERROR può essere utilizzata per controllare come applicationhandles nessun dato o qualsiasi altro errore che potrebbe verificarsi.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Questo è il risultato.
EXEC uspGetContact @LastName="Job"
Questo è il risultato quando non viene trovato alcun dato.
Esempio 5 – stored procedure con una stored procedure di chiamata separata
Ecco un altro esempio in cui abbiamo due stored procedure., Il primo storedprocedure uspFindContact cerca il primo record con un record di indirizzo e quindi restituisce il ContactID alla stored procedure chiamante per visualizzare nuovamente le informazioni theperson e address.
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
Il codice seguente modifica la stored procedure uspGetContact che chiama Uspfindcontact e restituisce i recordset.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Questo è il risultato.
EXEC uspGetContact @LastName="Job"
Questo è il risultato.,
Esempio 6 – stored procedure con commenti
Quest’ultimo esempio prende la stored procedure uspGetContact e aggiunge commenti al codice in modo da poter vedere come funzionano i commenti all’interno di una stored procedure.
I commenti possono essere fatti in due modi
- usando/
- usando / * per iniziare il blocco dei commenti e * / per terminare il blocco dei commenti.
A parte questo non è cambiato nient’altro nella stored procedure.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
Questi sono esempi piuttosto semplici, ma si spera che questo ti dia un’idea di comefacile è creare stored procedure per SQL Server. Se è possibile eseguire una dichiarazione SELECT da una finestra di query o dall’applicazione, è possibile eseguire facilmente la procedura astored come mostrato sopra.,
Next Steps
- Se non si sta già utilizzando stored procedure si spera che questo ti dia qualche intuizione su cosa devi fare per iniziare a usarle
- Come menzionato questi sono esempi piuttosto semplici, ma qualsiasi cosa tu possa fare con un gruppo di istruzioni può essere combinato in una stored procedure e quindi utilizzato più e più volte per le tue applicazioni.,
- Controllare thestored procedura di esercitazione per ulteriori esempi
Ultimo Aggiornamento: 2020-07-30
Circa l’autore
Greg Robidoux è il Presidente di Edgewood Soluzioni e co-fondatore di MSSQLTips.com.Visualizzare tutti i miei consigli
- Più di Database Developer Consigli…