query - pedir parametros en sql server

cómo pasar parámetros para consultar en SQL(Excel)

Question

"Conecté" Excel a Sql y funcionó bien: escribí algunos scripts SQL y funcionó muy bien. Todo lo que quiero hacer es pasar el parámetro a consulta. Como cada vez que realizo una actualización, quiero poder pasar el parámetro (condición de filtro) a Sql Query. En "Propiedades de conexión" el botón Parámetros está deshabilitado. Entonces no puedo hacer la consulta de parámetros. ¿Alguien puede ayudarme?

Answer 1

Depende de la base de datos a la que intente conectarse, del método por el que creó la conexión y de la versión de Excel que está utilizando. (También, muy probablemente, la versión del controlador ODBC relevante en su computadora).

Los siguientes ejemplos usan SQL Server 2008 y Excel 2007, ambos en mi equipo local.

Cuando utilicé el Asistente de conexión de datos (en la pestaña Datos de la cinta de opciones, en la sección Obtener datos externos, en De otras fuentes), vi lo mismo que usted: el botón Parámetros estaba deshabilitado y agregó un parámetro al consulta, algo así como select field from table where field2 = ? , hizo que Excel se quejara de que no se había especificado el valor para el parámetro y que los cambios no se guardaron.

Cuando utilicé Microsoft Query (el mismo lugar que el Asistente de conexión de datos), pude crear parámetros, especificar un nombre para mostrarlos e ingresar valores cada vez que se ejecutó la consulta. Al mostrar las Propiedades de conexión para esa conexión, se habilita el botón Parámetros ... y los parámetros se pueden modificar y usar como yo quiera.

También pude hacer esto con una base de datos de Access. Parece razonable que Microsoft Query se pueda usar para crear consultas parametrizadas que lleguen a otros tipos de bases de datos, pero no puedo probarlo fácilmente en este momento.

Answer 2

Esta publicación es lo suficientemente antigua como para que esta respuesta sea poco útil para el OP, pero pasé una eternidad tratando de responder a esta misma pregunta, así que pensé en actualizarla con mis hallazgos.

Esta respuesta supone que ya tiene una consulta SQL en funcionamiento en su documento de Excel. Hay muchos tutoriales que le muestran cómo lograr esto en la web, y muchos que explican cómo agregar una consulta parametrizada a uno, excepto que ninguno parece funcionar para una consulta OLE DB existente .

Entonces, si a ti, como a mí, se le entregó un documento Excel heredado con una consulta activa, pero el usuario quiere poder filtrar los resultados según uno de los campos de la base de datos, y si tú, como yo, no somos ni Excel ni un gurú de SQL, esto podría ayudarte.

La mayoría de las respuestas web a esta pregunta parecen indicar que debe agregar un "?" En su consulta para que Excel le solicite un parámetro personalizado, o coloque el indicador o la referencia de celda entre [corchetes] donde debería estar el parámetro. Esto puede funcionar para una consulta ODBC, pero no parece funcionar para un DB OLE, devolviendo "Sin valor dado para uno o más parámetros requeridos" en la instancia anterior, y "Nombre de columna inválido ''xxxx''" u "Objeto desconocido" ''xxxx'' "en los dos últimos. Del mismo modo, el uso de los míticos botones "Parámetros ..." o "Editar consulta ..." tampoco es una opción, ya que parecen estar atenuados permanentemente en esta instancia. (Como referencia, estoy usando Excel 2010, pero con un Workbook de Excel 97-2003 (* .xls))

Sin embargo, lo que podemos hacer es agregar una celda de parámetro y un botón con una rutina simple para actualizar programáticamente nuestro texto de consulta.

Primero, agregue una fila encima de su tabla de datos externa (o donde sea) donde puede colocar un indicador de parámetro junto a una celda vacía y un botón (Desarrollador-> Insertar-> Botón (Control de formulario) - Es posible que necesite habilitar la pestaña Desarrollador , pero puedes averiguar cómo hacerlo en otro lugar), así:

A continuación, seleccione una celda en el área de Datos externos (azul), luego abra Datos-> Actualizar todo (menú desplegable) -> Propiedades de conexión ... para ver su consulta. El código de la siguiente sección asume que ya tiene un parámetro en su consulta (Propiedades de conexión-> Definición-> Texto de comando) en el formato "WHERE (DB_TABLE_NAME.Field_Name = ''Default Query Parameter'')" (incluidos los paréntesis). Claramente "DB_TABLE_NAME.Field_Name" y "Default Query Parameter" necesitarán ser diferentes en su código, basándose en el nombre de la tabla de la base de datos, el nombre del campo de valor de la base de datos (columna) y algún valor predeterminado para buscar cuando se abra el documento (si tienes un conjunto de actualización automática). Anote el valor "DB_TABLE_NAME.Field_Name", ya que lo necesitará en la siguiente sección, junto con el "Nombre de la conexión" de su consulta, que se puede encontrar en la parte superior del cuadro de diálogo.

Cierre las Propiedades de conexión y presione Alt + F11 para abrir el editor de VBA. Si aún no está en él, haga clic derecho en el nombre de la hoja que contiene su botón en la ventana "Proyecto" y seleccione "Ver código". Pegue el siguiente código en la ventana de código (se recomienda copiar, ya que las comillas simples / dobles son inciertas y necesarias).

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " ''" & Range("Cell reference").Value & "''" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

Reemplace "DB_TABLE_NAME.Field_Name" y "Connection name" (en dos ubicaciones) con sus valores (las comillas dobles y el espacio y el signo igual deben incluirse).

Reemplaza "Referencia de celda" con la celda donde irá tu parámetro (la celda vacía desde el principio) - la mía fue la segunda celda en la primera fila, así que puse "B1" (de nuevo, son necesarias las comillas dobles).

Guarde y cierre el editor de VBA.

Ingrese su parámetro en la celda apropiada.

Haga clic con el botón derecho en su botón para asignar el sub RefreshQuery como la macro, luego haga clic en su botón. ¡La consulta debería actualizarse y mostrar los datos correctos!

Notas: Usar el nombre completo del parámetro de filtro ("DB_TABLE_NAME.Field_Name =") solo es necesario si tiene uniones u otras ocurrencias de signos iguales en su consulta, de lo contrario, solo un signo igual sería suficiente, y el cálculo de Len () sería superfluo. Si su parámetro está contenido en un campo que también se usa para unir tablas, necesitará cambiar la línea "paramPosition = InStr (queryPreText, valueToFilter) + Len (valueToFilter) - 1" en el código a "paramPosition = InStr ( Derecha (.CommandText, Len (.CommandText) - InStrRev (.CommandText, "WHERE")), valueToFilter) + Len (valueToFilter) - 1 + InStr (.CommandText, "WHERE") "para que solo busque el valorToFilter después del "DONDE".

Esta respuesta se creó con la ayuda de "BaconBits" de datapig, donde encontré el código base para la actualización de la consulta.