Validation failed for parameter 'param2'. Invalid string

App Building
1

The following query is throwing an error in @param2:

DECLARE @Years int = {{txtStockYears.value}};

WITH YearlyAverageSales AS (
SELECT
npi.IdArticulo,
a.cantidad,
descripcion,
MAX(np.FechaEmision) as UltimaCompra,
SUM(npi.Cantidad) as SumCantidad
FROM
[dbo].[NotaPedido_Items] npi
INNER JOIN
[dbo].[Articulos] a on a.idArticulo = npi.IdArticulo
JOIN
[dbo].[NotaPedidos] np ON npi.IdNotaPedido = np.IdNotaPedido
WHERE
np.FechaEmision >= DATEADD(YEAR, -@Years, CAST(GETDATE() AS DATE))
GROUP BY
npi.IdArticulo,
a.cantidad,
a.descripcion
)

SELECT
a.idArticulo,
a.Codigo,
a.descripcion,
c.IdCategoria,
c.Descripcion,
a.cantidad AS cantidad_en_stock,
s.Name as Proveedor,
s.Country as PaisProveedor,
y.SumCantidad as cantidad_vendida_en_periodo,
CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
ELSE 0
END AS costo_de_oportunidad,
CASE
WHEN a.cantidad <= y.SumCantidad THEN 'Necesita Reposicion'
ELSE 'Hay stock suficiente para periodo'
END AS estado,
y.UltimaCompra as ultima_compra
FROM
YearlyAverageSales y
JOIN
[dbo].[Articulos] a ON y.IdArticulo = a.idArticulo
JOIN
[dbo].[Categorias] c on c.IdCategoria = a.IdCategoria
JOIN
[dbo].[Suppliers] s on s.InternalId = a.proveedor
WHERE
a.discontinuado = 0 AND
((LEN('{{filterCategory.value}}') = 0) OR (a.IdCategoria in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterCategory.value}}', '[', ''), ']', ''), ','))))
AND ((LEN('{{filterProvider.value}}') = 0) OR (a.Proveedor in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterProvider.value}}', '[', ''), ']', ''), ','))))
AND ((LEN('{{filterCountry.value}}') = 0) OR (s.Country in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterCountry.value}}', '[', ''), ']', ''), ','))))
AND ({{ chkNeedRestock.value ? 0 : 1}} = 1 OR a.cantidad <= y.SumCantidad)
order by CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
end desc

The rendered query is as follows:

With params:

DECLARE @Years int = {{txtStockYears.value}};

WITH YearlyAverageSales AS (
SELECT
npi.IdArticulo,
a.cantidad,
descripcion,
MAX(np.FechaEmision) as UltimaCompra,
SUM(npi.Cantidad) as SumCantidad
FROM
[dbo].[NotaPedido_Items] npi
INNER JOIN
[dbo].[Articulos] a on a.idArticulo = npi.IdArticulo
JOIN
[dbo].[NotaPedidos] np ON npi.IdNotaPedido = np.IdNotaPedido
WHERE
np.FechaEmision >= DATEADD(YEAR, -@Years, CAST(GETDATE() AS DATE))
GROUP BY
npi.IdArticulo,
a.cantidad,
a.descripcion
)

SELECT
a.idArticulo,
a.Codigo,
a.descripcion,
c.IdCategoria,
c.Descripcion,
a.cantidad AS cantidad_en_stock,
s.Name as Proveedor,
s.Country as PaisProveedor,
y.SumCantidad as cantidad_vendida_en_periodo,
CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
ELSE 0
END AS costo_de_oportunidad,
CASE
WHEN a.cantidad <= y.SumCantidad THEN 'Necesita Reposicion'
ELSE 'Hay stock suficiente para periodo'
END AS estado,
y.UltimaCompra as ultima_compra
FROM
YearlyAverageSales y
JOIN
[dbo].[Articulos] a ON y.IdArticulo = a.idArticulo
JOIN
[dbo].[Categorias] c on c.IdCategoria = a.IdCategoria
JOIN
[dbo].[Suppliers] s on s.InternalId = a.proveedor
WHERE
a.discontinuado = 0 AND
((LEN('{{filterCategory.value}}') = 0) OR (a.IdCategoria in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterCategory.value}}', '[', ''), ']', ''), ','))))
AND ((LEN('{{filterProvider.value}}') = 0) OR (a.Proveedor in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterProvider.value}}', '[', ''), ']', ''), ','))))
AND ((LEN('{{filterCountry.value}}') = 0) OR (s.Country in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('{{filterCountry.value}}', '[', ''), ']', ''), ','))))
AND ({{ chkNeedRestock.value ? 0 : 1}} = 1 OR a.cantidad <= y.SumCantidad)
order by CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
end desc

and actual data:

DECLARE @Years int = 2;

WITH YearlyAverageSales AS (
SELECT
npi.IdArticulo,
a.cantidad,
descripcion,
MAX(np.FechaEmision) as UltimaCompra,
SUM(npi.Cantidad) as SumCantidad
FROM
[dbo].[NotaPedido_Items] npi
INNER JOIN
[dbo].[Articulos] a on a.idArticulo = npi.IdArticulo
JOIN
[dbo].[NotaPedidos] np ON npi.IdNotaPedido = np.IdNotaPedido
WHERE
np.FechaEmision >= DATEADD(YEAR, -@Years, CAST(GETDATE() AS DATE))
GROUP BY
npi.IdArticulo,
a.cantidad,
a.descripcion
)

SELECT
a.idArticulo,
a.Codigo,
a.descripcion,
c.IdCategoria,
c.Descripcion,
a.cantidad AS cantidad_en_stock,
s.Name as Proveedor,
s.Country as PaisProveedor,
y.SumCantidad as cantidad_vendida_en_periodo,
CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
ELSE 0
END AS costo_de_oportunidad,
CASE
WHEN a.cantidad <= y.SumCantidad THEN 'Necesita Reposicion'
ELSE 'Hay stock suficiente para periodo'
END AS estado,
y.UltimaCompra as ultima_compra
FROM
YearlyAverageSales y
JOIN
[dbo].[Articulos] a ON y.IdArticulo = a.idArticulo
JOIN
[dbo].[Categorias] c on c.IdCategoria = a.IdCategoria
JOIN
[dbo].[Suppliers] s on s.InternalId = a.proveedor
WHERE
a.discontinuado = 0 AND
((LEN('') = 0) OR (a.IdCategoria in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('', '[', ''), ']', ''), ','))))
AND ((LEN('') = 0) OR (a.Proveedor in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('', '[', ''), ']', ''), ','))))
AND ((LEN('') = 0) OR (s.Country in (SELECT value FROM STRING_SPLIT(REPLACE(REPLACE('', '[', ''), ']', ''), ','))))
AND (1 = 1 OR a.cantidad <= y.SumCantidad)
order by CASE
WHEN y.SumCantidad > a.cantidad THEN (y.SumCantidad-a.Cantidad) * a.preciounitario
end desc

I ran the query in SQL and works fine.
Any thoughts?

2

Hi! I have the exact same issue.
Is something wrong with Retool? Suddenly this appeared when running query I have run thousands times before.

3

Hey Diego,

We rolled out a feature flag which I believe was causing this error. We confirmed that reverting the flag fixed the issue for Jonas, can you confirm if this is now working for you also? (I rolled the flag back for all orgs)

4

Problem still happens when I do this:

EXEC Spisa_Stock
{{txtStockYears.value}},
'{{filterCategory.value.toString()}}',
'{{filterProvider.value.toString()}}',
'{{filterCountry.value.toString()}}',
{{chkNeedRestock.value ? 0 : 1}}

but if I remove quotes, it works fine.

EXEC Spisa_Stock
{{txtStockYears.value}},
{{filterCategory.value.toString()}},
{{filterProvider.value.toString()}},
{{filterCountry.value.toString()}},
{{chkNeedRestock.value ? 0 : 1}}

Thoughts?