Anche se non è la cosa migliore da fare con le stored procedure, talvolta si rende necessario realizzare delle procedure dinamiche.
Ci sarebbero diversi modi per poterle realizzare ma qui riporto solo quello che a mio avviso è quello più corretto da seguire.
Attraverso la stored procedure di sistema "sp_executesql" è possibile eseguire istruzioni dinamiche o batch Transact-SQL, la cosa importante da sottolineare è che attraverso questa procedura di sistema è possibile scrivere query con parametri incorporati.
La sintassi è la seguente:
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
L'utilizzo di questa stored procedure prevede alcuni requisiti che sono:
1. Il Parametro @stmt deve essere una stringa Unicode contenente un'istruzione o un batch Transact-SQL, pertanto deve essere una costante o una variabile Unicode. Come dicevo prima, la cosa importante da sottolineare è che @stmt può contenere parametri con lo stesso formato nativo, ad esempio: N'SELECT * FROM tabella WHERE ID = @ID'
2. Anche il formato di questa stringa deve essere Unicode e contiene le definizioni di tutti i parametri che sono presenti in @stmt. Anche qui è possibile utilizzare una costante o una variabile.
Elenco delle variabili - valori presenti nel parametro @stmt, i valori non sono necessari se l'istruzione o il batch Transact-SQL in @stmt non prevede parametri. Ovviamente anche i parametri di OUTPUT sono ammessi aggiungendone l'indicazione accanto ad ogni variabile.
L'esecuzione di sp_executesql restituisce tutti i risultati delle istruzioni SQL definite nel parametro @stmt.
Riporto alcune necessarie considerazioni:
1. Le istruzione presenti nel parametro @stmt di sp_executesql non vengono compilate ed eseguite finché non viene eseguita l'istruzione sp_executesql.
2. Per il parametro @stmt viene fatto un piano di esecuzione distinto dal piano di esecuzione del batch che ha chiamato sp_executesql questo vuol dire che fra l'ambiente del chiamante e l'ambiente generato dalla "sp_executesql" non è condiviso niente. Tuttavia una parte di questo problema potrebbe essere risolta ma non ne accenno in questo articolo.
3. Voglio infine ricordare che essendo valori di tipo Unicode quelli che devono essere passati alla "sp_executesql", il limite massimo delle nostre query sarà quello di 4000 caratteri, ossia il limite massimo di un nvarchar su una macchina moderna. Ovviamente anche per questo limite è possibile porre parzialmente rimedio.
Qui sotto riporto un esempio contenente anche parametri di output:
create procedure [dbo].[usp_Procedure] (@par1 int, @par2 int, @par3 char(3) output) as
declare @sql nvarchar(4000)
declare @ParmDefinition nvarchar(1000)
SET @ParmDefinition = N'@par1 int, @par2 int, @par3 char(3) output'
--in questa parte si può complicare la procedura secondo necessità concatenando le varie parti delle vostre query
set @sql='
SELECT @par3 = campo3 from tabella
WHERE campo1 = @par1 and campo2 = @par2'
set @sql = @sql + ' ORDER BY campo1'
--print @sql
exec sp_executesql @sql, @ParmDefinition,
@par1 = @par1,
@par2 = @par2,
@par3 = @par3 output
