SQL Server Performance

Using a fieldname in a variable in a SELECT list?

Discussion in 'T-SQL Performance Tuning for Developers' started by rock_and_roll, Oct 10, 2007.

  1. rock_and_roll New Member

    I apologise if this is ludicrously easy, but I've searched for the answer and not found it.
    Without exactly why I want to do this, I just want to write a loop that cycles through these fieldnames:
    popr_cp1, popr_cp2, popr_cp3.......popr_cp10
    Here's a rough and basic idea of what I imagined the code to look like:

    DECLARE @counter INT, @cost CHAR(6)

    SET @counter = 1

    WHILE @counter <= 10

    SET @cost = 'cost' + STR(@counter)

    SELECT [product], [supplier], @cost
    FROM suppliercosts

    SET @counter = @counter + 1
    Of course, the code sees the variable @cost as a value, not as a fieldname in the suppliercosts table.
    What do I do to tell T-SQL that the variable is a fieldname and not a value?

    Be gentle............I'm only little
  2. rock_and_roll New Member

    By the way, I know I can do this with 10 SELECT statements, but I wanted something a little more elegant...........
  3. FrankKalis Moderator

  4. rock_and_roll New Member

    Many thanks FrankKalis for your help. It is very much appreciated.

    I have gone away and read about dynamic queries and re-written the code to use that and it works perfectly.

    I note that some say to avoid using dynamic queries, but it doesn't matter so much in this instance. The code is for converting data from one format to another as part of a conversion project. Once I'm happy with the code, it's a one-off run. So, there are no performance issues to consider.

    Thanks again.......I learned something new today......

  5. satya Moderator

Share This Page