Di: Greg Robidoux
Panoramica
Il vero potere delle stored procedure è la capacità di passare i parametri e havethe stored procedure gestire le diverse richieste che vengono fatte. In thistopic esamineremo il passaggio dei valori dei parametri a una stored procedure.
Spiegazione
Proprio come hai la possibilità di utilizzare i parametri con il tuo codice SQL puoi anche impostare le stored procedure per accettare uno o più valori di parametro. Tutti gli esempi utilizzano il database di AdventureWorks.,
Creazione di una Stored Procedure SQL con parametri
- Per creare una stored procedure con parametri utilizzando la seguente sintassi:
- CREA PROCEDURA dbo.uspGetAddress @ City nvarchar (30) COME
- Vedi dettagli ed esempi sotto
Query SQL Server per trasformarsi in una Stored Procedure
Di seguito è riportata la query che vogliamo utilizzare per creare la stored procedure.
USE AdventureWorksGOSELECT * FROM Person.AddressGO
L’idea è di creare la stored procedure in cui la Città viene passata nella procedura memorizzata in modo che possa creare risultati dinamici., Questo può essere fatto come segue usandouna variabile. Se eseguiamo il codice sottostante restituirà solo i risultati per NewYork.
USE AdventureWorksGODECLARE @City nvarchar(30)SET @City = 'New York'SELECT * FROM Person.AddressWHERE City = @CityGO
Potremmo usare questo approccio e continuare ad aggiornare la variabile @City, ma c’è un modo migliore per farlo creando una stored procedure.
Crea Stored Procedure SQL Server con un parametro
In questo esempio interrogheremo la Persona.Tabella degli indirizzi da AdventureWorksdatabase, ma invece di recuperare tutti i record lo limiteremo a un particularcity., Questo esempio presuppone che ci sarà una corrispondenza esatta sul valore della cittàche viene passato.
USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)ASSELECT * FROM Person.AddressWHERE City = @CityGO
chiamare la stored procedure viene eseguita come segue:
EXEC dbo.uspGetAddress @City = 'New York'
Eliminare la Stored Procedure
Se si è creato la stored procedure e si desidera ricreare la stored procedure con lo stesso nome, è possibile eliminarlo utilizzando il followingbefore cercando di creare di nuovo.
USE AdventureWorksGODROP PROCEDURE dbo.uspGetAddressGO
Se si tenta di creare la stored procedure ed esiste già si otterrà un messaggio di errore.,
C’è già un oggetto chiamato ‘uspGetAddress’ nel database.
Stored Procedure SQL Server con parametro utilizzando Jolly
Possiamo anche fare la stessa cosa, ma consentire agli utenti di darci un punto di partenzaper cercare i dati.
Qui possiamo cambiare ” = “in un LIKE e usare il carattere jolly”%”.
Questo può essere eseguito come segue per trovare tutte le città che iniziano con nuove.,
EXEC dbo.uspGetAddress @City = 'New'
Errore di stored Procedure SQL Server Quando il parametro non viene passato
In entrambi gli esempi di procedimento si presuppone che un valore del parametro venga sempre passato. Se si tenta di eseguire la procedura senza passare un parametervalue, verrà visualizzato un messaggio di errore come il seguente:
La procedura o la funzione ‘uspGetAddress’ prevede il parametro ‘@City’, che non è stato fornito.,
Stored Procedure SQL Server utilizzando NULL come parametro predefinito
Nella maggior parte dei casi è sempre una buona pratica passare tutti i valori dei parametri,ma a volte non è possibile. Quindi in questo esempio usiamo l’opzione NULLPER consentirti di non passare un valore di parametro. Se creiamo ed eseguiamo questo storedprocedure così com’è, non restituirà alcun dato, perché sta cercando qualsiasi Cityvalues uguale a NULL.
USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULLASSELECT *FROM Person.AddressWHERE City = @CityGO
Se eseguiamo quanto segue funzionerà, ma nessun dato verrà restituito.,
EXEC dbo.uspGetAddress
Potremmo cambiare questa stored procedure e utilizzare la funzione ISNULL per ottenere aroundthis. Quindi, se viene passato un valore, utilizzerà il valore per restringere il resultset e se un valore non viene passato restituirà tutti i record. (Nota: se la colonna City ha valori NULL, questo non includerà questi valori. Dovrai aggiungere ulteriore logica per City IS NULL)
Ora se eseguiamo il comando sottostante, tutti i dati verranno restituiti dalla tabella.,
EXEC dbo.uspGetAddress
Creare Stored Procedure SQL Server con più parametri
Impostare più parametri è molto facile da fare. Hai solo bisogno di elencareogni parametro e il tipo di dati separati da una virgola come mostrato di seguito.
Per eseguire questa operazione è possibile eseguire una delle seguenti operazioni:
Ultimo aggiornamento: 4/3/2020