How to select a column with a string

Question

This is my code:

declare @MaxPointNumber INT=499, @pointNumber INT = 5;
select ('point'+CAST(@pointNumber as varchar))
from #TempHold

And this is the result:

point5 should be the name of column, but somehow it becomes a new value in the table. Can anyone help me understand what is going on?

Answer 1

As stated earlier you need dynamic sql

DECLARE @sql VARCHAR(200)
DECLARE @MaxPointNumber INT=499, @pointNumber INT = 5;
SET @sql = 'SELECT ' + ('point'+CAST(@pointNumber as varchar)) +
' from #TempHold'


EXEC (@sql)