sp_executesql
L’alternativa principale all’uso di EXECUTE() è la stored procedure sp_executesql (a seguire SPEXEC).
Come EXECUTE() anche SPEXEC permette la valutazione e l’esecuzione di stringhe a runtime.
A differenza di EXECUTE(), sp_executesql accetta due parametri.
Il primo parametro è @stmt nvarchar(max) che contiene il corpo della query da eseguire, il secondo è @params nvarchar(max), parametro opzionale che contiene una lista di parametri e valori.
La sintassi di @params è la stessa prevista per la lista dei parametri di una SP.
Il tipo di entrambi i parametri, nvarchar, rappresenta la versione unicode del parametro varchar.
Un limite sostanziale nell'uso dei parametri da parte di sp_executesql risiede nel fatto che non è possibile parametrizzare nomi di oggetti SQL Server come tabelle, colonne, trigger, funzioni eccetera.
Ciò significa che una query del tipo:
SELECT * FROM @NomeTabella
eseguita con SPEXEC, va gestita alla stessa maniera di EXECUTE() cioè con la concatenazione piuttosto che con la parametrizzazione.
Fin'ora abbiamo esaminato piccole differenze e analogie sintattiche tra i due comandi, ma ciò che rende qualitativamente diversi i due approcci è il fatto che SPEXEC richiede che nella chiamata vengano dichiarati e valorizzati i singoli parametri passati dall’utente.
Vediamo un esempio creando una nuova SP che a fronte di un parametro @ProdottoId restituisca il record corrispondente dalla tabella Prodotti.
CREATE PROCEDURE GetProdotto
@ProdottoId as int
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = N'SELECT * FROM Prodotti WHERE ProdottoId = @ProdottoId'
EXEC sp_executesql @sql, N'@ProdottoId int', @ProdottoId = @ProdottoId
END
Va notato innanzitutto come il parametro venga inserito all'interno della stringa e non concatenato ad essa (WHERE ProdottoId = @ProdottoId).
Al momento dell'esecuzione di sp_executesql il parametro viene dichiarato, qualificato (acquisendo quindi un tipo certo) e valorizzato.
Il fatto di definire il parametro dello scope dinamico con lo stesso nome del suo corrispettivo esterno, non comporta problemi per il funzionamento della query e anzi rappresenta una best practice per migliorare la leggibilità e la manutenibilità del codice. Essendo gli scope separati la coincidenza del nome dei parametri non dà origine a collisioni.
I lettori più accorti si chiederanno quale sia lo scopo reale di sp_executesql visto che la query in esempio può essere riscritta anche senza ricorrere a codice dinamico.
Per chiarirci le idee proviamo ad immaginare che la nostra SP debba ritornare non tanto un elemento della tabella Prodotti ma un elemento estratto da una qualsiasi tabella, dato il nome della tabella, il nome della colonna chiave e l'id dell'elemento.
CREATE PROCEDURE GetItem
@NomeTabella as varchar(50),
@NomeChiave as varchar(50),
@ItemId as int
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = N'SELECT * FROM ' +
quotename(@NomeTabella) + ' WHERE ' +
quotename(@NomeChiave) + ' = @ItemId'
EXEC sp_executesql @sql, N'@ItemId int', @ItemId = @ItemId
END
Procediamo con l'esecuzione della SP.
exec GetItem @NomeTabella = 'Prodotti', @NomeChiave = 'ProdottoId', @ItemId = 1
Come si può notare dal codice della SP, anche se SPEXEC non può parametrizzare i nomi oggetto (tabella e campo chiave) siamo riusciti ugualmente a costruire una query dinamica con nomi oggetto variabili facendo sì che poi fosse sp_executesql ad eseguire il tutto aggiungendo il parametro @ItemId.
E stavolta la query non può essere costruita che con codice dinamico.