0

I'm trying to do a SQL query on a priority matrix. So I want to pass the column name dynamically and then also filter based on a where clause that also has a dynamic value.

I've found some answers on Stackoverflow that have been useful but they've only got me so far, and now I'm a bit stuck. Can anyone see where I'm going wrong?

Query I have the issue with:

DECLARE @var1 VARCHAR(50)
DECLARE @var2 VARCHAR(50)
DECLARE @sql VARCHAR(255)

SET @var1 = 'partial impact'
SET @var2 = 'single user'
SET @sql = 'select [' + @var1 + '] from [priority matrix] where impact is ['+ @var2 + ']'

EXECUTE(@sql);

Query that isn't dynamic and works fine: 

SELECT [partial impact] 
FROM priorityMatrix 
WHERE impact = 'single user';

Error I'm seeing when I run it:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'single user'.

Stackoverflow queries that have helped so far:

SQL Server + Dynamic Query 'Invalid Column Name' How to set variable from a SQL query?

hlh3406
  • 1,382
  • 5
  • 29
  • 46
  • Have you tried to involve the equality symbol as you did in the *query that isn't dynamic*? Currently, you have an `is` there... In addition, the table name you are selecting from is not the same as it is in the working query. – deHaar Feb 19 '20 at 10:06

1 Answers1

3

IS <> =, so you need to fix it :

SET @sql = 'select [' + @var1 + '] from [priority matrix] where impact  = ['+ @var2 + ']';

I would use QUOTENAME() instead of manual [] :

SET @sql = 'SELECT '+ QUOTENAME(@var1) +' FROM [priority matrix] WHERE impact  = '''+@var2+'''';

Literal strings are quoted with single quotes, [] are not required.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • It is not known if the value of `@var2` can be supplied by the end user. If so, any single quote characters in the value of `@var2` should be doubled (`'` -> `''`) to avoid SQL injection attacks: `WHERE impact = ''' + REPLACE(@var2, '''', '''''') + ''''` – Bart Hofland Feb 19 '20 at 10:19