Poner parámetros a una consulta nativa desde una celda Excel

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Buenas y Saludos a los miembros y visitantes del foro


Tengo una Query para extrae determinados datos de una base de datos SQL Server por medio de una consulta nativa quisiera que alguien me ayudara a ponerle parámetros a esa consulta desde una celda de una hoja de Excel.

CodigoDescripcion
2332200125
estos valores estan en una hoja de Excel



aquÃ* solo pongo las partes de la Query que necesito quisiera que el valor de la celda código se convierta en el valor de la variable @ExistCod




DECLARE @ExistCod Varchar(20) Set @ExistCod = '2332200125'






SELECT
prod.codigo AS Codigo,


WHERE


prod.codigo = @ExistCod


Muchas Gracias de Antemano
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Lo siento, no hablo español (estoy usando el traductor de Google), pero si entiendo correctamente, estás intentando pasar parámetros a SQL Server. Le recomiendo que eche un vistazo al artículo de Chris Webb aquí, ya que cubre cómo hacer esto correctamente con la función Value.NativeQuery(). ¡Buena suerte!
 

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Buenas Tardes yo no hablo ingles y también uso el traductor de Google.
El link al que me envías no contiene lo que estoy tratando de hacer.

Lo que quisiera hacer es que la query que estoy ejecutando en una consulta nativa me extraiga los datos que le pido desde una celda de una hoja de Excel.
Si pudiera ayudarme se lo agradecería he buscado mucho y no encuentro la forma de hacerlo.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Para ser honesto, creo que el concepto en ese artículo es exactamente lo que necesitas. Aquí está el marco general que construí aquí:

1) Cree una consulta que se conecte a la raíz de la base de datos. En el libro adjunto, el mío se llama "Database". Para crearlo, me conecté a mi base de datos SQL de Azure, seleccioné una tabla y, a continuación, eliminé el paso "Navegación". Luego cargué eso como una consulta de "Solo conexión".

2) Creé mi consulta SQL personalizada en una celda de tabla de Excel. Observe que también agregué un número QueryID, ya que esto es importante para profundizar para obtener una consulta "Escalar" que se trata como texto, en lugar de una tabla. Una vez que extraje esa tabla en Power Query, filtré la consulta al QueryID correcto, eliminé la columna QueryID, luego hice clic con el botón secundario en el texto SQL y elegí profundizar.

3) Finalmente, construí la consulta para usar la función Value.NativeQuery(). Para hacer esto, hice clic derecho en la consulta "Base de datos" y elegí Hacer referencia a ella (no Duplicar). Luego hice clic en el botón fx junto a la barra de fórmulas para obtener un nuevo paso. Para ese paso, utilicé la siguiente fórmula:
Code:
Value.NativeQuery(Database,SQLQuery)
Tenga en cuenta que deberá establecer los niveles de privacidad de sus orígenes de datos de manera coherente (usé Organización tanto para el libro como para la base de datos), así como decirle a Excel que aprueba la consulta de base de datos nativa para ejecutarse. Pero en ese punto, siempre que su consulta SQL sea válida, debería funcionar sin presionar el firewall de fórmulas.

Si desea probar esto, puede usar la base de datos que proporcionamos como muestra para mi libro. Puede encontrar esas credenciales cerca de la parte inferior de esta página.

¡Espero que esto ayude!
 

Attachments

  • 2022 Example - Value_NativeQuery.xlsx
    26.6 KB · Views: 7

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Buenas tardes perdone tanta molestia

La query que tengo es la siguiente y esta en una consulta nativa quisiera algo como lo que me envió pero no con la Query sino solo con un valor o parámetro.

Código
2252010010
este de arriba seria el valor a buscar

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF


DECLARE @ExistCod Varchar(20) Set @ExistCod = ((((( AQUI ES DONDE QUIERO PONER EL VALOR DE LA CELDA QUE ESTA EN LA HOJA DE EXCEL)))))






SELECT
prod.codigo AS Codigo,
prod.descripcion AS "Descripcion del Producto",


alm.codigo AS "Codigo Almacen",
alm.nombre AS "Nombre Almacen",
cat.nombre AS "Categoria",


umed.clave AS "Um",
umed.descripcion AS "Unidad Medida",
almdoc.fecha AS "Fecha",


ex.cantidad,
ISNULL( exres.cantidad, 0 ) AS "Reserva",
ex.importemn AS "Importe CUP",


ex.importemlc AS "Importe CUC",
uc.nombre AS "Nombre Unidad",
uc.codigo AS "Codigo Unidad"


FROM
inv_existencia ex
LEFT JOIN inv_existenciareserva exres ON exres.idexistencia = ex.idexistencia


INNER JOIN gen_medida umed ON umed.idmedida = ex.idmedida
INNER JOIN inv_categoria cat ON cat.idcategoria = ex.idcategoria


INNER JOIN gen_producto prod ON prod.idproducto = ex.idproducto
INNER JOIN inv_existenciaalm exalm ON exalm.idexistencia = ex.idexistencia


INNER JOIN gen_almacen alm ON alm.idalmacen = exalm.idalmacen
INNER JOIN gen_unidadcontable uc ON uc.idunidad = alm.idunidad


INNER JOIN (
SELECT
daml.idalmacen,
MAX ( idoc.fecha ) AS Fecha
FROM


inv_documentoalm daml
INNER JOIN inv_documento idoc ON idoc.iddocumento = daml.iddocumento
GROUP BY


daml.idalmacen
) almdoc ON almdoc.idalmacen = exalm.idalmacen
WHERE
uc.activo = 1


AND alm.activo = 1
AND prod.activo = 1
AND ex.cantidad > 0
AND prod.codigo = @ExistCod
ORDER BY


"Codigo Almacen" ASC,
codigo ASC

Por favor perdone mi falta de conocimiento y muchas gracias
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hola Josue, entiendo lo que estás tratando de hacer aquí, pero mi consejo será crear toda la consulta usando una fórmula de Excel, luego pasarla a la función Value.NativeQuery(). Si intenta hacer esto de manera diferente, violará el firewall. Ya es bastante difícil resolver esos problemas en inglés, y mucho menos cuando estamos tratando de conversar a través de Google Translate. :)

Echa un vistazo a lo que hice en el archivo que he adjuntado a esta publicación. Observe cómo cambié mi tabla para dividir el SQL en pedazos. Esto es lo que te sugiero que hagas en tu mesa. A continuación, vuelva a armarlo para ver la consulta completa en la primera columna de mi tabla. (Simplemente no olvide poner un espacio entre los componentes a los que desea unirse).



También he añadido otra hoja de cálculo al archivo, donde he desglosado su consulta como quiera (una celda para cada parte de la consulta). Eso me permite usar fórmulas para completar la línea DECLARE correctamente, y luego puedo volver a juntarla toda con una fórmula TEXTJOIN en una tabla. Verá la tabla en la parte superior y los componentes debajo de eso.


Una cosa a tener en cuenta... si su columna en SQL es texto, es posible que también deba ajustar la línea declare para agregar caracteres alrededor del código. Para ello, establezca la fórmula en C11 para que diga lo siguiente:

Code:
="DECLARE @ExistCod Varchar(20) Set @ExistCod =[COLOR=#ff0000] [FONT=arial black]'[/FONT][/COLOR]"&C9[COLOR=#ff0000][FONT=arial black]&"'"[/FONT][/COLOR]

También querrá tener en cuenta que, en la función TEXTJOIN, el delimitador que utiliza es un salto de línea (es decir, el CHAR(10) ), seguido de un espacio. Esto es solo para asegurarse de que la instrucción SQL siga siendo válida.

Sé que parece extraño hacerlo en la tabla de Excel, pero sugerirá que este es el mejor lugar para hacerlo. Si tiene la mayor parte de su SQL en Power Query, podrá combinar las columnas para compilar la consulta, pero esto es mucho más fácil de mover a la función Value.NativeQuery() en mi opinión.

Y recuerde que, si es necesario, puede copiar la celda de la tabla y pegarla en SQL Server para verificar que el SQL se forma correctamente.
 

Attachments

  • Josue.xlsx
    31 KB · Views: 6

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Muchísimas gracias con esa ayuda que me ha prestado ya resolví el problema
 

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Buenas Tardes
Si pudiera ayudarme a quitar este mensaje que aparece cada vez que actualizo una consulta con el método que me usted enseño.
Yo Uso Office 2019
Saludos
281188i344FE3AC2CCA77B1
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
No conozco la ruta del menú en español para esto, pero desea desmarcar la opción Power Query que requiere la aprobación del usuario para nuevas consultas de base de datos. En inglés, haría clic en:

Get Data --> Query Options --> Security

Y luego desmarca la casilla que indica en la imagen.
2022-01-27_11-32-25.png
 

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Muchas gracias nuevamente sin Adularlo usted es un maestro
Quisiera saber si la consulta que le envié antes y me ayudo a que funcionara se puede hacer en una consulta que no sea nativa y que funciones de igual manera
Saludos
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Lo que Power Query quiere decir con una "consulta nativa" es cualquier consulta que esté escrita en SQL. La única forma de evitarlo es conectarse a la base de datos con Power Query (sin rellenar ese cuadro SQL) y, a continuación, realizar todas las transformaciones a través de la interfaz de Power Query.

Creo que podría evitar la consulta nativa, pero puede desencadenar un problema diferente: el firewall de fórmulas. Esto se debe a que está intentando combinar dos orígenes de datos para hacerlo.

Honestamente, si esto está funcionando, probablemente será más rápido de ejecutar, ya que está pasando una instrucción SQL completa, lo que significa que Power Query no tiene que interpretarla o evaluarla en absoluto.
 

Josue Alba

New member
Joined
Jan 26, 2022
Messages
8
Reaction score
0
Points
0
Excel Version(s)
2019
Buenos días Ken

Quisiera si no le es molestia me pudiera decir si a esa consulta le puedo definir parámetros de conexión incluyendo nombre de usuario y contraseña. y si pudiera decirme los pasos a seguir.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Buenos días. Lo sentimos, no es posible incluirlos en la consulta. Así no es como funciona Power Query. La autenticación se administra en la máquina de cada usuario la primera vez que se conecta y se almacena en caché en su dispositivo. A cada nuevo usuario y nuevo dispositivo se le pedirá que ingrese sus credenciales. No hay forma de evitarlo.
 
Top