Declare variable in Grafana query from dashboard variable

Question

I am using a table visualization in Grafana and I want to filter the content of the table with a variable called "PC", of type query, showing a list of PC names.

The SQL sentence that I am using is the following:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
DECLARE @PC AS NVARCHAR(255) = $PC;
DECLARE @pcFilter AS NVARCHAR(255) = '%' + @PC + '%'

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(PC)
                   FROM DATOS_GENERALES DG INNER JOIN OPERACION_MANUAL OM ON DG.RefId = OM.RefId
                   WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE @pcFilter
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

SET @query = 'SELECT Fecha, ' + @cols + ' FROM
            (SELECT PC, CONVERT(NVARCHAR(8), TimeStamp_UTC, 112) AS Fecha, 
            (TiempoTest + 2 * Tiempo)/3600 AS TiempoTotal
            FROM DATOS_GENERALES DG INNER JOIN OPERACION_MANUAL OM ON DG.RefId = OM.RefId
            WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE @pcFilter) AS A
            PIVOT
            (
                SUM(TiempoTotal) FOR PC IN (' + @cols + ')
            ) AS P
            ORDER BY Fecha DESC'
/* Original execution
EXECUTE(@query); 
*/
-- Currently using execution
EXECUTE sp_executesql @query, N'@pcFilter nvarchar(255)', @pcFilter = @pcFilter

The variable pcFilter is declared in order to take the value of the Grafana's dashboard variable PC and then added to the WHERE conditions.

Without declaring the variable and including the WHERE condition the table is correctly shown, but with the code from above I get this error:

db query error: mssql: Must declare the scalar variable "@pcFilter".

Am I declaring in a wrong way this variable? If so, how should I do it?

Have you checked if this query (with `$PC` manually substituted by its value from Grafana) works using some db client? Could you also share the initial query? — markalex, Mar 24 '23 at 09:57
The @query has @pcFilter which is not declared anywhere when `EXECUTE(@query)`..similar to `EXECUTE('select @pcFilter')`. You could use sp_executesql to execute the @query and declare @pcFilter...`exec sp_executesql @query, N'@pcFilter nvarchar(255)', @pcFilter = @pcFilter` — lptr, Mar 24 '23 at 13:35
Still getting the same error. The query inspector shows this result once the filtering is applied to choose 3 different PCs: `DECLARE @PC AS NVARCHAR(255) = 'LPHQ0141','LPHQ0308','PCHQ0395'; DECLARE @pcFilter AS NVARCHAR(255) = '%' + @PC + '%';` — Jose Mari Muguruza, Mar 27 '23 at 07:28

score 0 · Answer 1 · answered Mar 27 '23 at 07:42

0

Problem lies within your generated statement.

Basically, at the last line of script you provided dbms tries so execute something like

SELECT Fecha, col1, col2, col3 FROM
            (SELECT PC, CONVERT(NVARCHAR(8), TimeStamp_UTC, 112) AS Fecha, 
            (TiempoTest + 2 * Tiempo)/3600 AS TiempoTotal
            FROM DATOS_GENERALES DG INNER JOIN OPERACION_MANUAL OM ON DG.RefId = OM.RefId
            WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE @pcFilter) AS A
            PIVOT
            (
                SUM(TiempoTotal) FOR PC IN (col1, col2, col3)
            ) AS P
            ORDER BY Fecha DESC

but variable @pcFilter is undeclared here (because EXECUTE statement has it's own scope).

You either need to pass a declaration of @pcFilter into your @query, or better yet change line

WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE @pcFilter) AS A

to

WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE ''' + @pcFilter + ''') AS A

answered Mar 27 '23 at 07:42

markalex

8,623
2
7
32

Same result when substituting `@pcFilter` by `''' + @pcFilter + '''` in `@query` – Jose Mari Muguruza Mar 27 '23 at 08:47
Could you print generated this way query and try execute it independently? – markalex Mar 27 '23 at 08:54
Sure. `DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) DECLARE @PC AS NVARCHAR(255) = $PC; DECLARE @pcFilter AS NVARCHAR(255) = '%' + @PC + '%'; SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(PC) FROM DATOS_GENERALES DG INNER JOIN OPERACION_MANUAL OM ON DG.RefId = OM.RefId WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE @pcFilter FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')`... – Jose Mari Muguruza Mar 28 '23 at 10:18
...`SET @query = 'SELECT Fecha, ' + @cols + ' FROM (SELECT PC, CONVERT(NVARCHAR(8), TimeStamp_UTC, 112) AS Fecha, (TiempoTest + 2*Tiempo)/3600 AS TiempoTotal FROM DATOS_GENERALES DG INNER JOIN OPERACION_MANUAL OM ON DG.RefId = OM.RefId WHERE TimeStamp_UTC IS NOT NULL AND PC LIKE ''' + @pcFilter + ''') AS A PIVOT (SUM(TiempoTotal) FOR PC IN (' + @cols + ')) AS P ORDER BY Fecha DESC' EXECUTE sp_executesql @query, N'@pcFilter nvarchar(255)', @pcFilter = @pcFilter` – Jose Mari Muguruza Mar 28 '23 at 10:19
1

I mean instead of `EXECUTE` use `Print` to see what `@query` is generated. And then take this query and execute it yourself. – markalex Mar 28 '23 at 10:31
When substituting manually one value in `$PC` (for example `DECLARE @PC AS NVARCHAR(255) = 'LPHQ0141'`) it prints the query as expected and also executing the query works. However, when adding manually another value in the second line, when declaring `@PC`, it fails. – Jose Mari Muguruza Mar 28 '23 at 10:56

Declare variable in Grafana query from dashboard variable

1 Answers1