Partilhar via

  • Artigo

Trabalhando com parâmetros e códigos de retorno na tarefa Executar SQL

As instruções SQL e os procedimentos armazenados frequentemente usam parâmetros input, parâmetros output e códigos de retorno. No Integration Services, a tarefa Executar SQL tem suporte para os tipos de parâmetros Input, Output e ReturnValue. Use o tipo Input para parâmetros de entrada, Output para parâmetros de saída e ReturnValue para códigos de retorno.

ObservaçãoObservação

Você só poderá usar parâmetros em uma tarefa Executar SQL se o provedor de dados der suporte a eles.

Os parâmetros em comandos SQL, inclusive consultas e procedimentos armazenados, são mapeados para variáveis definidas pelo usuário criadas no escopo da tarefa Executar SQL, um contêiner pai ou no escopo do pacote. Os valores das variáveis podem ser definidos em tempo de projeto ou populados dinamicamente em tempo de execução. Você também pode mapear parâmetros para variáveis de sistema. Para obter mais informações, consulte Variáveis do Integration Services e Variáveis do sistema.

No entanto, trabalhar com parâmetros e códigos de retorno em uma tarefa Executar SQL é mais do que apenas saber para quais tipos de parâmetro a tarefa tem suporte e como esses parâmetros serão mapeados. Há requisitos de uso adicionais e diretrizes para usar parâmetros e códigos de retorno de modo bem-sucedido na tarefa Executar SQL. Esses requisitos de uso e diretrizes são abordados no restante deste tópico:

  • Usando nomes e marcadores de parâmetros

  • Usando parâmetros com tipos de dados de data e hora

  • Usando parâmetros em cláusulas WHERE

  • Usando parâmetros com procedimentos armazenados

  • Obtendo valores de códigos de retorno

  • Configurando parâmetros e códigos de retorno no Editor de Tarefa Executar SQL

Usando nomes e marcadores de parâmetros

Dependendo do tipo de conexão usado pela tarefa Executar SQL, a sintaxe do comando SQL utiliza marcadores de parâmetro diferentes. Por exemplo, o tipo de gerenciador de conexões ADO.NET exige que o comando SQL use um marcador de parâmetro no formato @varParameter, enquanto que o tipo de conexão OLE DB exige o marcador de parâmetro ponto de interrogação (?). marcador de parâmetro.

Os nomes que você pode usar como nomes de parâmetro nos mapeamentos entre as variáveis e os parâmetros também variam por tipo de gerenciador de conexões. Por exemplo, o tipo de gerenciador de conexão ADO.NET usa um nome definido pelo usuário com um prefixo @, enquanto o tipo de gerenciador de conexões OLE DB requer que você use o valor numérico de ordinal de base 0 como o nome do parâmetro.

A tabela a seguir resume os requisitos de comandos SQL para os tipos de gerenciador de conexões que podem ser utilizados pela tarefa Executar SQL.

Tipo de conexão

Marcador de parâmetro

Nome do parâmetro

Exemplo de comando SQL

ADO

?

Param1, Param2,...

SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

ADO.NET

@<nome_do_parâmetro>

@<nome_do_parâmetro>

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 = ?

Usando parâmetros com os gerenciadores de conexões ADO.NET e ADO

Os gerenciadores de conexões ADO.NET e ADO têm requisitos específicos para comandos SQL que usam parâmetros:

  • Os gerenciadores de conexões ADO.NET requerem que o comando SQL use nomes como marcadores de parâmetro. Isso significa que as variáveis podem ser mapeadas diretamente para parâmetros. Por exemplo, a variável @varName é mapeada para o parâmetro denominado @parName e fornece um valor para o parâmetro @parName.

  • Os gerenciadores de conexões ADO requerem que o comando SQL use pontos de interrogação (?) como marcadores de parâmetro. No entanto, você pode usar qualquer nome definido pelo usuário, com exceção de valores de número inteiro, como nomes de parâmetro.

Para fornecer valores a parâmetros, as variáveis são mapeadas para nomes de parâmetro. Em seguida, a tarefa Executar SQL usa o valor ordinal do nome do parâmetro na lista de parâmetros para carregar valores de variáveis a parâmetros.

Usando parâmetros com os gerenciadores de conexões EXCEL, ODBC e OLE DB

Os gerenciadores de conexões EXCEL, ODBC e OLE DB requerem que o comando SQL use pontos de interrogação (?) como marcadores de parâmetro e valores numéricos baseados em 0 ou 1 como nomes de parâmetro. Se a tarefa Executar SQL utilizar o gerenciador de conexões ODBC, o nome do parâmetro mapeado para o primeiro parâmetro na consulta será denominado 1; caso contrário, o parâmetro será denominado 0. Para os parâmetros subsequentes, o valor numérico do nome do parâmetro indica o parâmetro no comando SQL para o qual o nome do parâmetro é mapeado. Por exemplo, o parâmetro denominado 3 é mapeado para o terceiro parâmetro, que é representado pelo terceiro ponto de interrogação (?) no comando SQL.

Para fornecer valores a parâmetros, as variáveis são mapeadas para nomes de parâmetros e a tarefa Executar SQL usa o valor ordinal do nome do parâmetro para carregar valores de variáveis a parâmetros.

Dependendo do provedor utilizado pelo gerenciador de conexões, alguns tipos de dados OLE DB talvez não tenham suporte. Por exemplo, o driver do Excel reconhece apenas um conjunto limitado de tipos de dados. Para obter mais informações sobre o comportamento do provedor Jet com o driver do Excel, consulte Origem do Excel.

Usando parâmetros com gerenciadores de conexões OLE DB

Quando a tarefa Executar SQL usa o gerenciador de conexões OLE DB, a propriedade BypassPrepare da tarefa está disponível. Defina esta propriedade como true se a tarefa Executar SQL usar instruções SQL com parâmetros.

Quando você usa um gerenciador de conexões OLE DB, não pode usar subconsultas com parâmetros, porque a tarefa Executar SQL não pode derivar informações de parâmetro por meio do provedor OLE DB. Entretanto, você pode usar uma expressão para concatenar os valores de parâmetro na cadeia de caracteres de consulta de definir a propriedade SqlStatementSource da tarefa.

Usando parâmetros com tipos de dados de data e hora

Usando parâmetros de data e hora com os gerenciadores de conexões ADO.NET e ADO

Ao ler dados de tipos SQL Servertime e datetimeoffset, uma tarefa Executar SQL que usa um gerenciador de conexões ADO.NET ou ADO tem os seguintes requisitos adicionais:

  • Em dados de time, um gerenciador de conexões ADO.NET requer que esses dados sejam armazenados em um parâmetro cujo tipo é Input ou Output, e esse tipo de dados é string.

  • Para dados de datetimeoffset, um gerenciador de conexões ADO.NET requer que esses dados sejam armazenados em um dos seguintes parâmetros:

    • Um parâmetro cujo tipo é Input e cujo tipo de dados é string.

    • Um parâmetro cujo tipo é Output ou ReturnValue, e cujo tipo de dados é datetimeoffset, string ou datetime2. Se você selecionar um parâmetro cujo tipo de dados é string ou datetime2, o Integration Services converterá os dados em string ou datetime2.

  • Um gerenciador de conexões ADO requer que os dados time ou datetimeoffset sejam armazenados em um parâmetro cujo tipo é Input ou Output e cujo tipo de dados é adVarWchar.

Para obter mais informações sobre tipos de dados SQL Server e como eles são mapeados para tipos de dados Integration Services, consulte Tipos de dados (Transact-SQL) e Tipos de dados do Integration Services.

Usando parâmetros de data e hora com os gerenciadores de conexões OLE DB

Quando você usa um gerenciador de conexões OLE DB, uma tarefa Executar SQL tem requisitos de armazenamento específicos para os dados nos tipos de dados SQL Server, date, time, datetime, datetime2 e datetimeoffset. Você deve armazenar estes dados em um dos tipos de parâmetros seguintes:

  • Um parâmetro de entrada do tipo de dados NVARCHAR.

  • Um parâmetro de saída com o tipo de dados apropriado, como listado na tabela a seguir.

    Tipo de parâmetro Output

    Tipo de dados de data

    DBDATE

    date

    DBTIME2

    time

    DBTIMESTAMP

    datetime, datetime2

    DBTIMESTAMPOFFSET

    datetimeoffset

Se os dados não forem armazenados no parâmetro de entrada ou saída apropriado, o pacote falhará.

Usando parâmetros de data e hora com gerenciadores de conexões ODBC

Quando você usa um gerenciador de conexões ODBC, uma tarefa Executar SQL tem requisitos de armazenamento específicos para dados com um dos tipos de dados SQL Server, date, time, datetime, datetime2 ou datetimeoffset. Você deve armazenar estes dados em um dos tipos de parâmetros seguintes:

  • Um parâmetro input do tipo de dados SQL_WVARCHAR

  • Um parâmetro de output com o tipo de dados apropriado, como listado na tabela a seguir.

    Tipo de parâmetro Output

    Tipo de dados de data

    SQL_DATE

    date

    SQL_SS_TIME2

    time

    SQL_TYPE_TIMESTAMP

    - ou -

    SQL_TIMESTAMP

    datetime, datetime2

    SQL_SS_TIMESTAMPOFFSET

    datetimeoffset

Se os dados não forem armazenados no parâmetro de entrada ou saída apropriado, o pacote falhará.

Usando parâmetros em cláusulas WHERE

Os comandos SELECT, INSERT, UPDATE e DELETE frequentemente incluem cláusulas WHERE para especificar filtros que definem as condições que cada linha nas tabelas de origem devem atender para se qualificar para um comando SQL. Os parâmetros fornecem os valores de filtro nas cláusulas WHERE.

Você pode usar marcadores de parâmetro para fornecer valores de parâmetros dinamicamente. As regras para quais marcadores e nomes de parâmetros podem ser usados na instrução SQL dependem do tipo de gerenciador de conexões utilizado por Executar SQL.

A tabela a seguir lista exemplos do comando SELECT por tipo de gerenciador de conexões. As instruções INSERT, UPDATE e DELETE são semelhantes. Os exemplos usam SELECT para retornar produtos da tabela Product em AdventureWorks2008R2 que tenham uma ProductID maior e menor que os valores especificados pelos dois parâmetros.

Tipo de conexão

Sintaxe de 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

Os exemplos exigiriam parâmetros que têm os seguintes nomes:

  • Os gerenciadores de conexões EXCEL e OLED DB usam os nomes de parâmetro 0 e 1. O tipo de conexão ODBC usa 1 e 2.

  • O tipo de conexão ADO pode usar quaisquer dois nomes de parâmetro, como Param1 e Param2, mas os parâmetros devem ser mapeados pela posição original na lista de parâmetros.

  • O tipo de conexão ADO.NET usa os nomes de parâmetro @parmMinProductID e @parmMaxProductID.

Usando parâmetros com procedimentos armazenados

Os comandos SQL que executam procedimentos armazenados também podem usar mapeamento de parâmetro. As regras de como usar marcadores e nomes de parâmetros dependem do tipo de gerenciador de conexões utilizado por Executar SQL, assim como as regras para consultas parametrizadas.

A tabela a seguir lista exemplos do comando EXEC por tipo de gerenciador de conexões. Os exemplos executam o procedimento armazenado uspGetBillOfMaterials em AdventureWorks2008R2. O procedimento armazenado usa os parâmetros @StartProductID e @CheckDateinput.

Tipo de conexão

Sintaxe de EXEC

EXCEL e OLEDB

EXEC uspGetBillOfMaterials ?, ?

ODBC

{call uspGetBillOfMaterials(?, ?)}

Para obter mais informações sobre a sintaxe de chamada ODBC, consulte o tópico Procedure Parameters em ODBC Programmer's Reference na MSDN Library.

ADO

Se IsQueryStoredProcedure for definido como False, EXEC uspGetBillOfMaterials ?, ?

Se IsQueryStoredProcedure for definido como True, uspGetBillOfMaterials

ADO.NET

Se IsQueryStoredProcedure for definido como False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

Se IsQueryStoredProcedure for definido como True, uspGetBillOfMaterials

Para usar parâmetros de saída, a sintaxe requer que a palavra-chave OUTPUT seja colocada após cada marcador de parâmetro. Por exemplo, a sintaxe do parâmetro de saída a seguir está correta: EXEC myStoredProcedure ? OUTPUT.

Para obter mais informações sobre como usar parâmetros de entrada e saída com procedimentos armazenados Transact-SQL, consulte Parâmetros (Mecanismo de Banco de Dados), Retornando dados por meio de parâmetros OUTPUT e EXECUTE (Transact-SQL).

Obtendo valores de códigos de retorno

Um procedimento armazenado pode retornar um valor inteiro chamado código de retorno para indicar o status de execução de um procedimento. Para implementar códigos de retorno na tarefa Executar SQL, use parâmetros do tipo ReturnValue.

A tabela a seguir lista, por tipo de conexão, alguns exemplos de comandos EXEC que implementam códigos de retorno. Todos os exemplos usam um parâmetro input. As regras de uso de marcadores e nomes de parâmetro são as mesmas para todos os tipos de parâmetros — Input, Output e ReturnValue.

Algumas sintaxes não dão suporte a literais de parâmetro. Nesse caso, você deve fornecer o valor dó parâmetro usando uma variável.

Tipo de conexão

Sintaxe de EXEC

EXCEL e OLEDB

EXEC ? = myStoredProcedure 1

ODBC

{? = call myStoredProcedure(1)}

Para obter mais informações sobre a sintaxe de chamada ODBC, consulte o tópico Procedure Parameters em ODBC Programmer's Reference na MSDN Library.

ADO

Se IsQueryStoreProcedure for definido como False, EXEC ? = myStoredProcedure 1

Se IsQueryStoreProcedure for definido como True, myStoredProcedure

ADO.NET

Se IsQueryStoreProcedure for definido como True.

myStoredProcedure

Na sintaxe mostrada na tabela anterior, a tarefa Executar SQL usa o tipo de fonte Entrada Direta para executar o procedimento armazenado. A tarefa Executar SQL também pode usar o tipo de fonte Conexão de Arquivo para executar um procedimento armazenado. Quer a tarefa Executar SQL use o tipo de fonte Entrada Direta ou Conexão de Arquivo, use um parâmetro do tipo ReturnValue para implementar o código de retorno. Para obter mais informações sobre como configurar o tipo de fonte da instrução SQL executado pela tarefa Executar SQL, consulte Editor da Tarefa Executar SQL (página Geral).

Para obter mais informações sobre como usar códigos de retorno com procedimentos armazenados Transact-SQL, consulte Retornando dados usando um código de retorno e RETURN (Transact-SQL).

Configurando parâmetros e códigos de retorno na Tarefa Executar SQL

Para obter mais informações sobre as propriedades de parâmetros e códigos de retorno que podem ser definidas no Designer SSIS, clique no seguinte tópico:

Para obter mais informações sobre como definir essas propriedades no SSIS Designer, clique no tópico a seguir:

Recursos externos

Ícone do Integration Services (pequeno) Fique atualizado com o Integration Services

Para obter os mais recentes downloads, artigos, exemplos e vídeos da Microsoft, bem como soluções exclusivas da comunidade, visite a página do Integration Services no MSDN ou TechNet:

Para receber uma notificação automática das atualizações, assine os feeds RSS disponíveis na página.