Dynamic Query in SQL Server

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.

  • What happened with SQL English query?
  • Required cte implementation for hierarchical data
  • How to query the database with a list of key-value pairs
  • SQL Query calculation across tables
  • How to EXEC a stored procedure using values from a table
  • 'Auto Reconnect' JDBC connection property for Microsoft JDBC Driver
  • 9 Solutions collect form web for “Dynamic Query in SQL Server”

    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.

    Yes, using a CASE statement:

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

    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.

    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.

    Good article for people looking to do dynamic sql:

    The Curse and Blessings of Dynamic SQL

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

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

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

    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()

    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();
    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.