SQL Server Query using a Dynamic Column Name

Question

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?

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
– deHaar, Commented Feb 19, 2020 at 10:06

Yogesh Sharma · Accepted Answer · 2020-02-19 10:16:41Z

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.

edited Feb 19, 2020 at 10:16

answered Feb 19, 2020 at 10:09

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bart Hofland Over a year ago

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, '''', '''''') + ''''

Collectives™ on Stack Overflow

SQL Server Query using a Dynamic Column Name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest