Dynamic Query in SQL Server

Question

I have a table with 10 columns as col_1,col_2,.... col_10. I want to write a select statement that will select a value of one of the row and from one of these 10 columns. I have a variable that will decide which column to select from. Can such query be written where the column name is dynamically decided from a variable.

Answer 1

Yes, using a CASE statement:

SELECT CASE @MyVariable
       WHEN 1 THEN [Col_1]
       WHEN 2 THEN [Col_2]
       ...
       WHEN 10 THEN [Col_10]
       END

Whether this is a good idea is another question entirely. You should use better names than Col_1, Col_2, etc.

You could also use a string substitution method, as suggested by others. However, that is an option of last resort because it can open up your code to sql injection attacks.