Post reply

Selecting a dynamic column name

  • December 28, 2006 at 9:22 am

    #171653

    So here is an interesting one. We have a table that can have different columns based on the client it is running for (ex. client 1 has CRI, CCH, CWR, and client 2 has CTY, CHH, CIU). I need to select one of those columns. I have tried setting a variable to the column name and doing a SELECT @Col FROM table WHERE..., but all it returns is the value in the variable (ie. 'CRI'). How can I select a value in a dynamic column name?

    Thanks

  • December 28, 2006 at 9:43 am

    #680304

    Select case when @SomeVar = 'Something' THEN CRI ELSE CTY END AS Col1,... FROM dbo.YourTable.

     

    Need I point out that this is the wrong design for the system?

  • December 28, 2006 at 10:56 am

    #680321

    oh, the design of the system is a topic best left alone.

    unfortunately, you suggestion won't work because the column names are unknown at design time (more may be added during the year). they can't be hard coded into the SP or function.

  • December 28, 2006 at 11:42 am

    #680333

    In that case you should use dynamic sql... prepare the statement with and execute it using EXEC () OR SP_EXECUTESQL...

     

    MohammedU
    Microsoft SQL Server MVP

  • December 28, 2006 at 11:52 am

    #680335

    yeah, that's the route i'm going down now. looks like that will do the trick

  • December 28, 2006 at 3:54 pm

    #680393

    Keep in mind the performane and security issues with dynamic sql...

    The Curse and Blessings of Dynamic SQL

    http://www.sommarskog.se/dynamic_sql.html

     

    MohammedU
    Microsoft SQL Server MVP

  • December 29, 2006 at 7:10 am

    #680475

    Security shouldn't be an issue, as it will be within a SP or function. As for performance, well, the 'design' of our database squashes any hopes we might have...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply