Utilizzo di parametri e di codici restituiti nell'attività Esegui SQL
Le stored procedure e le istruzioni SQL utilizzano spesso parametri di input, parametri di outpute codici restituiti. In Integration Services l'attività Esegui SQL supporta parametri di tipo Input, Output e ReturnValue. Il tipo Input viene utilizzato per i parametri di input, il tipo Output per i parametri di output e il tipo ReturnValue per i codici restituiti.
Nota
È possibile utilizzare parametri in un'attività Esegui SQL solo se il provider di dati li supporta.
I parametri inclusi nei comandi SQL, comprese le query e le stored procedure, vengono mappati a variabili definite dall'utente create nell'ambito dell'attività Esegui SQL, in un contenitore padre o nell'ambito del pacchetto. I valori delle variabili possono essere impostati in fase di progettazione o popolati dinamicamente in fase di esecuzione. È inoltre possibile mappare parametri a variabili di sistema. Per ulteriori informazioni, vedere Variabili in Integration Services e Variabili di sistema.
Tuttavia, l'utilizzo di parametri e di codici restituiti in un'attività Esegui SQL non si limita solo alla conoscenza dei tipi di parametro supportati dall'attività e del modo in cui questi parametri vengono mappati. Sono previsti ulteriori requisiti e linee guida per utilizzare correttamente i parametri e i codici restituiti nell'attività Esegui SQL. Nella parte restante di questo argomento vengono illustrati tali requisiti e linee guida:
Utilizzo di nomi e indicatori di parametro
Utilizzo di parametri con i tipi di dati di data e ora
Utilizzo di parametri nelle clausole WHERE
Utilizzo di parametri con le stored procedure
Recupero dei valori dei codici restituiti
Configurazione di parametri e di codici restituiti nell'editor attività Esegui SQL
Utilizzo di nomi e indicatori di parametro
Nella sintassi del comando SQL possono essere utilizzati indicatori di parametro diversi, a seconda del tipo di connessione utilizzato dall'attività Esegui SQL. Per il tipo di gestione connessione ADO.NET, ad esempio, l'indicatore di parametro utilizzato nel comando SQL deve avere il formato @varParameter, mentre per il tipo di connessione OLE DB tale indicatore deve essere costituito da un punto interrogativo (?) .
Anche i nomi che è possibile utilizzare come nomi di parametro nei mapping tra variabili e parametri variano a seconda del tipo di gestione connessione. Il tipo di gestione connessione ADO.NET utilizza ad esempio un nome definito dall'utente con prefisso @, mentre il tipo di gestione connessione OLE DB richiede nomi di parametro costituiti dal valore numerico di un ordinale in base 0.
Nella tabella seguente sono riepilogati i requisiti dei comandi SQL, a seconda dei tipi di gestione connessione utilizzati dall'attività Esegui SQL.
Tipo di connessione |
Indicatore di parametro |
Nome di parametro |
Comando SQL di esempio |
|---|---|---|---|
ADO |
? |
Param1, Param2, … |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ? |
ADO.NET |
@<parameter name> |
@<parameter name> |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = @parmBusinessEntityID |
ODBC |
? |
1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ? |
EXCEL e OLE DB |
? |
0, 1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ? |
Utilizzo di parametri con le gestioni connessioni ADO.NET e ADO
Le gestioni connessioni ADO.NET e ADO hanno requisiti specifici per i comandi SQL che utilizzano parametri:
Le gestioni connessioni ADO.NET richiedono che il comando SQL utilizzi nomi di parametro come indicatori di parametro. È pertanto possibile mappare direttamente le variabili ai parametri. Se ad esempio la variabile @varName viene mappata a un parametro di nome @parName, fornirà il valore per il parametro @parName.
Per le gestioni connessioni ADO, è necessario che il comando SQL utilizzi punti interrogativi (?) come indicatori di parametro. Tuttavia, come nomi di parametro è possibile utilizzare qualsiasi nome definito dall'utente, ad eccezione dei valori integer.
Per fornire i valori ai parametri le variabili vengono mappate ai nomi di parametro. L'attività Esegui SQL utilizza quindi il valore ordinale del nome di parametro nell'elenco dei parametri per caricare i valori dalle variabili ai parametri.
Utilizzo di parametri con le gestioni connessioni EXCEL, ODBC e OLE DB
Per le gestioni connessioni EXCEL, ODBC e OLE DB, è necessario che il comando SQL utilizzi punti interrogativi (?) come indicatori di parametro e valori numerici su base 0 o su base 1 come nomi del parametro. Se l'attività Esegui SQL utilizza la gestione connessione ODBC, il nome del parametro mappato al primo parametro nella query è 1, altrimenti è 0. Per i parametri successivi, il valore numerico del nome del parametro indica il parametro del comando SQL a cui è mappato il nome di parametro. Il parametro di nome 3, ad esempio, viene mappato al terzo parametro, rappresentato dal terzo punto interrogativo (?) nel comando SQL.
Per fornire i valori ai parametri, le variabili sono mappate ai nomi di parametro e l'attività Esegui SQL utilizza il valore ordinale del nome di parametro per caricare i valori dalle variabili ai parametri.
A seconda del provider utilizzato dalla gestione connessione, alcuni tipi di dati OLE DB potrebbero non essere supportati. Il driver per Excel, ad esempio, riconosce solo un set limitato di tipi di dati. Per ulteriori informazioni sul comportamento del provider Jet utilizzato insieme al driver per Excel, vedere Origine Excel.
Utilizzo di parametri con le gestioni connessioni OLE DB
Quando l'attività Esegui SQL utilizza la gestione connessione OLE DB, è disponibile la proprietà BypassPrepare dell'attività. Questa proprietà deve essere impostata su true se l'attività Esegui SQL utilizza istruzioni SQL con parametri.
Quando si utilizza una gestione connessione OLE DB, non è possibile utilizzare sottoquery con parametri, perché l'attività Esegui SQL non può derivare le informazioni sui parametri tramite il provider OLE DB. Tuttavia, è possibile utilizzare un'espressione per concatenare i valori dei parametri nella stringa di query e impostare la proprietà SqlStatementSource dell'attività.
Utilizzo di parametri con i tipi di dati di data e ora
Utilizzo di parametri di data e ora con le gestioni connessioni ADO.NET e ADO
Durante la lettura dei tipi di dati di SQL Server, time e datetimeoffset, un'attività Esegui SQL che utilizza una gestione connessione ADO.NET o ADO prevede i requisiti aggiuntivi seguenti:
Per i dati time, una gestione connessione ADO.NET richiede che i dati vengano archiviati in un parametro con tipo di parametro Input o Output e con tipo di dati string.
Per i dati datetimeoffset, una gestione connessione ADO.NET richiede che i dati vengano archiviati in uno dei parametri seguenti:
Un parametro il cui tipo di parametro è Input e il cui tipo di dati è string.
Un parametro il cui tipo di parametro è Output o ReturnValue e il cui tipo di dati è datetimeoffset, string o datetime2. Se si seleziona un parametro il cui tipo di dati è string o datetime2, Integration Services converte i dati in string o datetime2.
Una gestione connessione ADO richiede che i dati time o datetimeoffset vengano archiviati in un parametro con tipo di parametro Input o Output e con tipo di dati adVarWchar.
Per ulteriori informazioni sui tipi di dati di SQL Server e sul modo in cui vengono mappati ai tipi di dati di Integration Services, vedere Tipi di dati (Transact-SQL) e Tipi di dati di Integration Services.
Utilizzo di parametri di data e ora con le gestioni connessioni OLE DB
Quando si utilizza una gestione connessione OLE DB, un'attività Esegui SQL prevede requisiti di archiviazione specifici per i tipi di dati di SQL Server, date, time, datetime, datetime2 e datetimeoffset. È necessario archiviare questi dati in uno dei seguenti tipi di parametro:
Un parametro di input del tipo di dati NVARCHAR.
Un parametro di output con il tipo di dati appropriato, come elencato nella tabella seguente.
Tipo di parametro Output
Tipo di dati date
DBDATE
date
DBTIME2
time
DBTIMESTAMP
datetime, datetime2
DBTIMESTAMPOFFSET
datetimeoffset
Se i dati non vengono archiviati nel parametro di input o di output appropriato, il pacchetto non viene eseguito correttamente.
Utilizzo di parametri di data e ora con le gestioni connessioni ODBC
Quando si utilizza una gestione connessione ODBC, un'attività Esegui SQL prevede requisiti di archiviazione specifici per i tipi di dati di SQL Server, date, time, datetime, datetime2 e datetimeoffset. È necessario archiviare questi dati in uno dei seguenti tipi di parametro:
Un parametro di input del tipo di dati SQL_WVARCHAR.
Un parametro di output con il tipo di dati appropriato, come elencato nella tabella seguente.
Tipo di parametro Output
Tipo di dati date
SQL_DATE
date
SQL_SS_TIME2
time
SQL_TYPE_TIMESTAMP
-oppure-
SQL_TIMESTAMP
datetime, datetime2
SQL_SS_TIMESTAMPOFFSET
datetimeoffset
Se i dati non vengono archiviati nel parametro di input o di output appropriato, il pacchetto non viene eseguito correttamente.
Utilizzo di parametri nelle clausole WHERE
I comandi SELECT, INSERT, UPDATE e DELETE includono spesso la clausola WHERE per specificare filtri che definiscono le condizioni che ogni riga nelle tabelle di origine deve soddisfare per essere qualificata per un comando SQL. I parametri specificano i valori del filtro per la clausola WHERE.
È possibile utilizzare indicatori di parametro per specificare dinamicamente i valori dei parametri. Le regole che determinano se è possibile utilizzare indicatori di parametro e nomi di parametro in un'istruzione SQL dipendono dal tipo di gestione connessione utilizzato dall'attività Esegui SQL.
Nella tabella seguente sono elencati esempi di comandi SELECT per tipo di gestione connessione. Le istruzioni INSERT, UPDATE e DELETE sono analoghe. Negli esempi l'istruzione SELECT viene utilizzata per recuperare dalla tabella Product del database AdventureWorks2008R2 i prodotti con ProductID compreso tra i valori specificati da due parametri.
Tipo di connessione |
Sintassi dell'istruzione SELECT |
|---|---|
EXCEL, ODBC e OLEDB |
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ? |
ADO |
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ? |
ADO.NET |
SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID |
Gli esempi richiedono parametri con i nomi seguenti:
Per le gestioni connessioni EXCEL e OLED DB vengono utilizzati i nomi di parametro 0 e 1. Per il tipo di connessione ODBC vengono utilizzati 1 e 2.
Per il tipo di connessione ADO è possibile utilizzare qualsiasi nome per i due parametri, ad esempio Param1 e Param2, ma entrambi i parametri devono essere mappati in base alla relativa posizione ordinale nell'elenco di parametri.
Per il tipo di connessione ADO.NET vengono utilizzati i nomi di parametro @parmMinProductID e @parmMaxProductID.
Utilizzo di parametri con le stored procedure
Anche i comandi SQL che eseguono stored procedure possono utilizzare il mapping dei parametri. Come avviene per le regole delle query con parametri, anche le regole che determinano la modalità di utilizzo di indicatori di parametro e nomi di parametro dipendono dal tipo di gestione connessione utilizzato dall'attività Esegui SQL.
Nella tabella seguente sono elencati esempi di comandi EXEC per tipo di gestione connessione. Gli esempi eseguono la stored procedure uspGetBillOfMaterials nel database AdventureWorks2008R2. Tale stored procedure utilizza i parametri di input@StartProductID e @CheckDate.
Tipo di connessione |
Sintassi dell'istruzione EXEC |
|---|---|
EXCEL e OLEDB |
EXEC uspGetBillOfMaterials ?, ? |
ODBC |
{call uspGetBillOfMaterials(?, ?)} Per ulteriori informazioni sulla sintassi ODBC, vedere l'argomento Procedure Parameters in ODBC Programmer's Reference in MSDN Library. |
ADO |
Se IsQueryStoredProcedure è impostato su False, EXEC uspGetBillOfMaterials ?, ? Se IsQueryStoredProcedure è impostato su True, uspGetBillOfMaterials |
ADO.NET |
Se IsQueryStoredProcedure è impostato su False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate Se IsQueryStoredProcedure è impostato su True, uspGetBillOfMaterials |
La sintassi per l'utilizzo dei parametri di output richiede che dopo ogni indicatore di parametro venga specificata la parola chiave OUTPUT. Ad esempio, la sintassi del parametro di output seguente è corretta: EXEC myStoredProcedure ? OUTPUT.
Per ulteriori informazioni sull'utilizzo di parametri di input e di output con le stored procedure Transact-SQL, vedere Parametri (Motore di database), Restituzione di dati utilizzando i parametri OUTPUT e EXECUTE (Transact-SQL).
Recupero dei valori dei codici restituiti
Una stored procedure può restituire un valore intero, denominato codice restituito, per indicare lo stato di esecuzione di una procedura. Per implementare codici restituiti nell'attività Esegui SQL, è necessario utilizzare parametri di tipo ReturnValue.
Nella tabella seguente sono elencati, per tipo di gestione connessione, esempi di comandi EXEC che implementano codici restituiti. In tutti gli esempi viene utilizzato un parametro di input. Le regole che determinano la modalità di utilizzo di indicatori di parametro e nomi di parametro sono identiche per tutti i tipi di parametro: Input, Output e ReturnValue.
In alcune sintassi non è supportato l'utilizzo di valori letterali come parametri. In tali casi è necessario specificare il valore del parametro utilizzando una variabile.
Tipo di connessione |
Sintassi dell'istruzione EXEC |
|---|---|
EXCEL e OLEDB |
EXEC ? = myStoredProcedure 1 |
ODBC |
{? = call myStoredProcedure(1)} Per ulteriori informazioni sulla sintassi ODBC, vedere l'argomento Procedure Parameters in ODBC Programmer's Reference in MSDN Library. |
ADO |
Se IsQueryStoreProcedure è impostato su False, EXEC ? = myStoredProcedure 1 Se IsQueryStoreProcedure è impostato su True, myStoredProcedure |
ADO.NET |
Se IsQueryStoreProcedure è impostato su True. myStoredProcedure |
Nella sintassi illustrata nella tabella precedente, l'attività Esegui SQL utilizza il tipo di origine Input diretto per eseguire la stored procedure. L'attività Esegui SQL può utilizzare anche il tipo di origine Connessione file per eseguire una stored procedure. Indipendentemente dal fatto che l'attività Esegui SQL utilizzi il tipo di origine Input diretto o Connessione file, utilizzare un parametro del tipo ReturnValue per implementare il codice restituito. Per ulteriori informazioni sulla configurazione del tipo di origine dell'istruzione SQL eseguita dall'attività Esegui SQL, vedere Editor attività Esegui XML (pagina Generale).
Per ulteriori informazioni sull'utilizzo di codici restituiti con le stored procedure Transact-SQL, vedere Restituzione di dati utilizzando un codice restituito e RETURN (Transact-SQL).
Configurazione di parametri e di codici restituiti nell'attività Esegui SQL
Per ulteriori informazioni sulle proprietà dei parametri e dei codici restituiti che è possibile impostare in Progettazione SSIS, fare clic sull'argomento seguente:
Per ulteriori informazioni sull'impostazione di queste proprietà in Progettazione SSIS, fare clic sull'argomento seguente:
Risorse esterne
Intervento nel blog Stored procedures with output parameters nel sito Web all'indirizzo blogs.msdn.com
Esempio CodePlex Execute SQL Parameters and Result Sets sul sito Web all'indirizzo msftisprodsamples.codeplex.com
|
.gif "Icona di Integration Services (piccola)")
Rimanere aggiornati con Integration ServicesVedere anche