Trabajar con parámetros y códigos de retorno en la tarea Ejecutar SQL
Las instrucciones SQL y los procedimientos almacenados suelen usar parámetros de input, parámetros de output entrada y códigos de retorno. En Integration Services, la tarea Ejecutar SQL admite los tipos de parámetros Input, Output y ReturnValue. Utilice el tipo Input para parámetros de entrada, Output para parámetros de salida y ReturnValue para códigos de retorno.
Nota
Solo puede usar parámetros en una tarea Ejecutar SQL si el proveedor de datos los admite.
Los parámetros de los comandos SQL, incluidas las consultas y los procedimientos almacenados, se asignan a las variables definidas por el usuario que se crean dentro del ámbito de la tarea Ejecutar SQL, un contenedor primario o dentro del ámbito del paquete. Los valores de las variables pueden establecerse en tiempo de diseño o rellenarse dinámicamente en tiempo de ejecución. También se pueden asignar parámetros a las variables del sistema. Para obtener más información, vea Variables de Integration Services y Variables del sistema.
Sin embargo, trabajar con parámetros y códigos de retorno en una tarea Ejecutar SQL implica algo más que simplemente saber qué tipos de parámetros admite la tarea y cómo se asignarán. Hay requisitos de uso adicionales e instrucciones que se deben seguir para utilizar correctamente los parámetros y códigos de retorno en la tarea Ejecutar SQL. El resto de este tema abarca estos requisitos de uso e instrucciones:
Usar nombres de parámetros y marcadores
Usar parámetros con tipos de datos de fecha y hora
Usar parámetros en cláusulas WHERE
Usar parámetros con procedimientos almacenados
Obtener valores de códigos de retorno
Configurar parámetros y códigos de retorno en el Editor de la tarea Ejecutar SQL
Usar nombres de parámetros y marcadores
En función del tipo de conexión que utiliza la tarea Ejecutar SQL, la sintaxis del comando SQL usa marcadores de parámetros diferentes. Por ejemplo, el tipo de administrador de conexiones ADO.NET requiere que el comando SQL utilice un marcador de parámetro con el formato @varParameter, mientras que el tipo de conexión OLE DB requiere el signo de interrogación (?) como marcador de parámetro.
Los nombres que puede utilizar como nombres de parámetros en las asignaciones entre variables y parámetros también varían según el tipo de Administrador de conexiones. Por ejemplo, el tipo de Administrador de conexiones ADO.NET utiliza un nombre definido por el usuario con el prefijo @, mientras que el tipo de Administrador de conexiones OLE DB requiere que se utilice el valor numérico de un ordinal basado en 0 como nombre de parámetro.
La tabla siguiente resume los requisitos de los comandos SQL para los tipos de Administrador de conexiones que puede utilizar la tarea Ejecutar SQL.
Tipo de conexión |
Marcador de parámetro |
Nombre del parámetro |
Comando SQL (ejemplo) |
|---|---|---|---|
ADO |
? |
Param1, Param2, … |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ? |
ADO.NET |
@<nombre de parámetro> |
@<nombre de 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 y OLE DB |
? |
0, 1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ? |
Usar parámetros con ADO.NET y administradores de conexión ADO
Los administradores de conexión ADO y ADO.NET tienen requisitos concretos para los comandos SQL que utilizan parámetros:
Los administradores de conexión ADO.NET requieren que el comando SQL utilice nombres de parámetros como marcadores de parámetros. Esto significa que las variables se pueden asignar directamente a los parámetros. Por ejemplo, la variable @varName se asigna al parámetro denominado @parName y proporciona un valor al parámetro @parName.
Los administradores de conexiones ADO requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros. Sin embargo, puede utilizar cualquier nombre definido por el usuario, salvo valores enteros, como nombres de parámetro.
Para proporcionar valores a los parámetros, se asignan variables a los nombres de parámetro. Después, la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro de la lista de parámetros para cargar los valores de las variables en los parámetros.
Usar parámetros con administradores de conexión en EXCEL, ODBC y OLE DB
Los administradores de conexiones Excel, ODBC y OLE DB requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros y valores numéricos basados en 0 o en 1 como nombres de parámetros. Si la tarea Ejecutar SQL utiliza el Administrador de conexiones ODBC, el nombre de parámetro que se asigna al primer parámetro en la consulta se denomina 1; de lo contrario, el parámetro se denomina 0. Para los parámetros siguientes, el valor numérico del nombre de parámetro indica el parámetro en el comando SQL al que se asigna el nombre de parámetro. Por ejemplo, el parámetro denominado 3 se asigna al tercer parámetro, que se representa con un signo de interrogación (?) en el comando SQL.
Para proporcionar valores a los parámetros, se asignan variables a los nombres de parámetros y la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro para cargar valores de variables a parámetros.
En función del proveedor que utiliza el Administrador de conexiones, es posible que no se acepten algunos tipos de datos OLE DB. Por ejemplo, el controlador de Excel reconoce solo un conjunto limitado de tipos de datos. Para obtener más información sobre el comportamiento del proveedor Jet con el controlador de Excel, vea Origen de Excel.
Usar parámetros con administradores de conexión OLE DB
Cuando la tarea Ejecutar SQL utiliza el Administrador de conexiones OLE DB, está disponible la propiedad BypassPrepare de la tarea. Debería establecer esta propiedad en true si la tarea Ejecutar SQL utiliza instrucciones SQL con parámetros.
Cuando se usa un Administrador de conexiones OLE DB, no se pueden utilizar subconsultas con parámetros, ya que la tarea Ejecutar SQL no puede derivar la información de los parámetros a través del proveedor OLE DB. Sin embargo, puede utilizar una expresión para concatenar los valores de los parámetros en la cadena de consulta y establecer la propiedad SqlStatementSource de la tarea.
Usar parámetros con tipos de datos de fecha y hora
Usar parámetros de fecha y hora con administradores de conexión ADO y ADO.NET
Al leer datos de los tipos SQL Server, time y datetimeoffset, una tarea Ejecutar SQL que utiliza el Administrador de conexiones ADO o ADO.NET tiene los requisitos adicionales siguientes:
Para los datos de tipo time, un administrador de conexiones de ADO.NET requiere que estos datos se almacenen en un parámetro cuyo tipo sea Input o Output, y cuyo tipo de datos sea string.
Con los datos datetimeoffset, un Administrador de conexiones ADO.NET requiere que estos datos estén almacenados en uno de los parámetros siguientes:
Un parámetro cuyo tipo de parámetro es Input y cuyo tipo de datos es string.
Un parámetro cuyo tipo de parámetro es Output o ReturnValue, y cuyo tipo de datos es datetimeoffset, string o datetime2. Si selecciona un parámetro cuyo tipo de datos es string o datetime2, Integration Services convierte los datos en string o datetime2.
Un administrador de conexiones ADO requiere que los datos de tipo time o datetimeoffset estén almacenados en un parámetro cuyo tipo de parámetro sea Input o Output, y cuyo tipo de datos sea adVarWchar.
Para obtener más información sobre los tipos de datos de SQL Server y cómo se asignan a los tipos de datos de Integration Services, vea Tipos de datos (Transact-SQL) y Tipos de datos de Integration Services.
Usar parámetros de fecha y hora con administradores de conexión OLE DB
Al utilizar un Administrador de conexiones OLE DB, una tarea Ejecutar SQL tiene requisitos de almacenamiento concretos para los datos de los tipos SQL Server, date, time, datetime, datetime2 y datetimeoffset. Debe almacenar estos datos en uno de los tipos de parámetros siguientes:
Un parámetro de entrada del tipo de datos NVARCHAR.
Un parámetro de salida del tipo de datos adecuado, tal y como se enumera en la tabla siguiente.
Tipo de parámetro Output
Tipo de datos de fecha
DBDATE
date
DBTIME2
time
DBTIMESTAMP
datetime, datetime2
DBTIMESTAMPOFFSET
datetimeoffset
Si los datos no están almacenados en el parámetro de entrada o de salida adecuado, se produce un error en el paquete.
Usar parámetros de fecha y hora con administradores de conexión ODBC
Al utilizar un Administrador de conexiones ODBC, una tarea Ejecutar SQL tiene requisitos de almacenamiento concretos para uno de los datos de los tipos SQL Server, date, time, datetime, datetime2 o datetimeoffset. Debe almacenar estos datos en uno de los tipos de parámetros siguientes:
Un parámetro de input del tipo de datos SQL_WVARCHAR
Un parámetro output del tipo de datos adecuado, tal y como se enumera en la tabla siguiente.
Tipo de parámetro de Output
Tipo de datos de fecha
SQL_DATE
date
SQL_SS_TIME2
time
SQL_TYPE_TIMESTAMP
O bien
SQL_TIMESTAMP
datetime, datetime2
SQL_SS_TIMESTAMPOFFSET
datetimeoffset
Si los datos no están almacenados en el parámetro de entrada o de salida adecuado, se produce un error en el paquete.
Usar parámetros en cláusulas WHERE
Los comandos SELECT, INSERT, UPDATE y DELETE suelen incluir cláusulas WHERE para especificar filtros que definen las condiciones que debe cumplir cada fila de las tablas de origen con el fin de satisfacer los requisitos de un comando SQL. Los parámetros proporcionan los valores de filtro en las cláusulas WHERE.
Puede utilizar marcadores de parámetros para proporcionar valores de parámetros de forma dinámica. Las reglas para los marcadores y nombres de parámetros que se pueden utilizar en la instrucción SQL dependen del tipo de Administrador de conexiones que utiliza la tarea Ejecutar SQL.
La tabla siguiente enumera ejemplos del comando SELECT por tipo de Administrador de conexiones. Las instrucciones INSERT, UPDATE y DELETE son similares. Los ejemplos utilizan el comando SELECT para devolver los productos de la tabla Product de AdventureWorks2008R2 que tienen un valor de ProductID mayor y menor que los valores especificados por dos parámetros.
Tipo de conexión |
Sintaxis de SELECT |
|---|---|
EXCEL, ODBC y OLE DB |
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 |
Los ejemplos necesitarán parámetros que tengan los siguientes nombres:
Los administradores de conexión en EXCEL y OLED DB utilizan los nombres de parámetro 0 y 1. El tipo de conexión ODBC utiliza 1 y 2.
El tipo de conexión ADO podría utilizar cualquier par de nombres de parámetros, como Param1 y Param2, pero los parámetros deben asignarse por su posición ordinal en la lista de parámetros.
El tipo de conexión de ADO.NET utiliza los nombres de parámetros @parmMinProductID y @parmMaxProductID.
Usar parámetros con procedimientos almacenados
Los comandos SQL que ejecutan procedimientos almacenados también pueden usar la asignación de parámetros. Las reglas sobre el uso de marcadores y nombres de parámetros dependen del tipo de Administrador de conexiones que utiliza la tarea Ejecutar SQL, del mismo modo que sucede con las consultas con parámetros.
La tabla siguiente enumera ejemplos del comando EXEC por tipo de Administrador de conexiones. Los ejemplos ejecutan el procedimiento almacenado uspGetBillOfMaterials en AdventureWorks2008R2. El procedimiento almacenado utiliza los parámetros @StartProductID y @CheckDate de input.
Tipo de conexión |
Sintaxis de EXEC |
|---|---|
EXCEL y OLE DB |
EXEC uspGetBillOfMaterials ?, ? |
ODBC |
{call uspGetBillOfMaterials(?, ?)} Para obtener más información sobre la sintaxis de la llamada ODBC, consulte el tema Parámetros de procedimientos, en la Referencia del programador de ODBC de MSDN Library. |
ADO |
Si IsQueryStoredProcedure se establece en False, EXEC uspGetBillOfMaterials ?, ? Si IsQueryStoredProcedure se establece en True, uspGetBillOfMaterials |
ADO.NET |
Si IsQueryStoredProcedure se establece en False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate Si IsQueryStoredProcedure se establece en True, uspGetBillOfMaterials |
Para utilizar parámetros de salida, la sintaxis requiere que la palabra clave OUTPUT siga a cada marcador de parámetro. Por ejemplo, la sintaxis del parámetro de salida siguiente es correcta: EXEC myStoredProcedure ? OUTPUT.
Para obtener más información sobre el uso de parámetros de entrada y salida con procedimientos almacenados de Transact-SQL, vea Parámetros (motor de base de datos), Devolver datos mediante parámetros OUTPUT y EXECUTE (Transact-SQL).
Obtener valores de códigos de retorno
Un procedimiento almacenado puede devolver un valor entero, denominado código de retorno, para indicar el estado de ejecución de un procedimiento. Para implementar códigos de retorno en la tarea Ejecutar SQL, debe utilizar los parámetros del tipo ReturnValue.
La tabla siguiente enumera, por tipo de conexión, algunos ejemplos de comandos EXEC que implementan códigos de retorno. Todos los ejemplos utilizan un parámetro de input. Las reglas del uso de marcadores y nombres de parámetros son las mismas para todos los tipos de parámetros: Input, Output y ReturnValue.
Algunas sintaxis no admiten literales en los parámetros. En tal caso, debe proporcionar el valor del parámetro mediante una variable.
Tipo de conexión |
Sintaxis de EXEC |
|---|---|
EXCEL y OLE DB |
EXEC ? = myStoredProcedure 1 |
ODBC |
{? = call myStoredProcedure(1)} Para obtener más información sobre la sintaxis de la llamada ODBC, consulte el tema sobre Parámetros de procedimientos, en la Referencia del programador de ODBC de MSDN Library. |
ADO |
Si IsQueryStoreProcedure se establece en False, EXEC ? = myStoredProcedure 1 Si IsQueryStoreProcedure se establece en True, myStoredProcedure |
ADO.NET |
Si IsQueryStoreProcedure se establece en True. myStoredProcedure |
En la sintaxis mostrada en la tabla anterior, la tarea Ejecutar SQL utiliza el tipo de origen Entrada directa para ejecutar el procedimiento almacenado. La tarea Ejecutar SQL también puede utilizar el tipo de origen Conexión de archivos para ejecutar un procedimiento almacenado. Con independencia de si la tarea Ejecutar SQL utiliza el tipo de origen Entrada directa o Conexión de archivos, use un parámetro del tipo ReturnValue para implementar el código de retorno. Para obtener más información sobre cómo configurar el tipo de origen de la instrucción SQL que la tarea Ejecutar SQL ejecuta, vea Editor de la tarea Ejecutar SQL (página General).
Para obtener más información sobre el uso de códigos de retorno con procedimientos almacenados de Transact-SQL, vea Devolver datos mediante un código de retorno y RETURN (Transact-SQL).
Configurar parámetros y códigos de retorno en la tarea Ejecutar SQL
Para obtener más información acerca de las propiedades de los parámetros y códigos de devolución que puede establecer en el Diseñador SSIS, haga clic en el tema siguiente:
Para obtener más información acerca de cómo establecer estas propiedades en el Diseñador SSIS, haga clic en el tema siguiente:
Recursos externos
Entrada de blog, Procedimientos almacenados con parámetros de salida (en inglés), en blogs.msdn.com
Ejemplo CodePlex, Ejecutar conjuntos de resultados y parámetros de SQL (en inglés), en msftisprodsamples.codeplex.com
|
.gif "Icono de Integration Services (pequeño)")
Manténgase al día con Integration ServicesVea también