Condividi tramite

  • Articolo

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.Contact WHERE ContactID = ?

ADO.NET

@<parameter name>

@<parameter name>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL e OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

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 insieme 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 AdventureWorks 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 AdventureWorks. 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

Icona di Integration Services (piccola) Rimanere aggiornati con Integration Services

Per informazioni sui download, gli articoli, gli esempi e i video Microsoft più recenti, nonché sulle soluzioni selezionate dalla community, visitare la pagina Integration Services su MSDN o TechNet:

Per ricevere notifica automatica su questi aggiornamenti, sottoscrivere i feed RSS disponibili nella pagina.