Create Dynamic Select Statement

brigadier — Thu, 17 May 2018 12:25:31 GMT

I am in the process of creating the access script for a QV template file. The intention is that when I create the report for real, so as to ensure that I don't overlook changing the filename everywhere that I need to, I have declared it as a variable at the start of my script. In SQL, the script looks like this:

DECLARE @filename nvarchar(30) = 'GLMONEY',

@sql_string nvarchar(200);

SET @sql_string = 'SELECT CASE WHEN ' + @filename + ' = 0 THEN ''USER'''

+ ' WHEN ' + @filename + ' = 1 THEN ''ADMIN'''

+ ' END AS ACCESS, UPPER(ntname) AS NTNAME'

+ ' FROM Qlikview_Access'

+ ' WHERE ' + @filename + ' is not null';

exec sp_executesql @sql_string;

The Qlikview_Access table currently has this format:

[dbo].[Qlikview_Access](

[NTName] [nvarchar](50) NOT NULL,

[activityfeed] [bit] NULL,

[GLmoney] [bit] NULL

)

So consequently the above query returns this:

ACCESS NTNAME

ADMIN BUCKSHOSP\PAUL.GARDNER

USER BUCKSHOSP\HELEN.FRANKLIN

USER BUCKSHOSP\ALAN.HUDSON

USER BUCKSHOSP\GREG.MTUNZI

But when I paste the script into the Access section of the hidden script, it errors with

Unknown statement

exec sp_executesql @sql_string

How can I achieve this in QV? All my searches direct me towards creating a stored procedure and calling that using the execute command with @filename as a parameter. But that seems somewhat OTT for what is quite a straightforward select statement.

topic Create Dynamic Select Statement in QlikView

Create Dynamic Select Statement