Dynamic Query in SQL Server

Question

Hi,

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

Since I don't have enough details, I can't give code. Instead, I'll explain.

Declare a string variable, something like:

declare @sql varchar(5000)

Set that variable to be the completed SQL string you want (as a string, and not actually querying... so you embed the row-name you want using string concatenation).

Then call: exec(@sql)

All set.

Answer 2

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.

Answer 3

Sounds like a bad, denormalized design to me.

I think a better one would have the table as parent, with rows that contain a foreign key to a separate child table that contains ten rows, one for each of those columns you have now. Let the parent table set the foreign key according to that magic value when the row is inserted or updated in the parent table.

If the child table is fairly static, this will work.

Answer 4

I assume you are running purely within Transact-SQL. What you'll need to do is dynamically create the SQL statement with your variable as the column name and use the EXECUTE command to run it. For example:

EXECUTE('select ' + @myColumn + ' from MyTable')

Answer 5

You can do it with a T-SQl CASE statement:

SELECT 'The result' =
   CASE 
     WHEN choice = 1 THEN col1
     WHEN choice = 2 THEN col2
     ...
   END
FROM sometable

Answer 6

IMHO, Joel Coehoorn's case statement is probably the best idea

... but if you really have to use dynamic SQL, you can do it with sp_executeSQL()

Answer 7

I have no idea what platform you are using but you can use Dynamic LINQ pretty easily to do this.

var query = context.Table
                   .Where( t => t.Id == row_id )
                   .Select( "Col_" + column_id );

IEnumerator enumerator = query.GetEnumerator();
enumerator.MoveNext();
object columnValue = enumerator.Current;

Presumably, you'll know which actual type to cast this to depending on the column. The nice thing about this is you get the parameterized query for free, protecting you against SQL injection attacks.

Answer 8

Good article for people looking to do dynamic sql:

The Curse and Blessings of Dynamic SQL

Answer 9

This isn't something you should ever need to do if your database is correctly designed. I'd revisit the design of that element of the schema to remove the need to do this.