Results 1 to 4 of 4

Thread: Selecting by column number, MS SQL 2000

  1. #1
    Join Date
    Aug 2003
    Location
    Troy NY
    Posts
    2

    Question Selecting by column number, MS SQL 2000

    Ok, so I need to select data from a column by its number. something like:

    SELECT COLUMN_NAME(@int_col_num) FROM MY_TABLE WHERE CONDITION = 'whatever'

    The problem being that there is no function called COLUMN_NAME, the closest thing I could find is COL_NAME but for some reason that won't work. when I do something like:

    SELECT COL_NAME(@table_id, @int) FROM TABLE WHERE CONDITION = 'whatever'

    the only thing I get back is the output of COL_NAME(@table_id, @int) almost like it ignored the word "FROM" and everything behind it.

    Is there any way I can select data by the column number and not by the column name??

    My DB system is MS SQL Server 2000.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Yes.

    you have to combine with object id

    here is the example


    create table X123 (id int, name char(10))
    insert into x123 select 1,'asa'
    insert into x123 select 2,'pia'
    insert into x123 select 3,'pin'
    insert into x123 select 4,'ahs'
    insert into x123 select 5,'bit'

    --to get the column name
    select col_name(Object_id('x123') ,1)
    select col_name(Object_id('x123') ,2)
    Last edited by MAK; 08-03-2003 at 08:54 PM.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    for getting values from such columns


    declare @query varchar(1000)
    set @query = 'select '+col_name(Object_id('x123') ,1)+' from x123'
    exec (@query)

  4. #4
    Join Date
    Aug 2003
    Location
    Troy NY
    Posts
    2

    Ok, but how do I assign that to a variable

    Thats great the EXEC('some query') works just fine, but how do I get the results of that query back into a variable? For example I can:

    DECLARE @var varchar(50)
    EXEC('SELECT @col_name FROM TABLE WHERE CONDITION = whatever')

    but It won't let me do something like
    SELECT @var2 = EXEC(@some_query)
    or
    SELECT @var2 = Select top 1* from (EXEC (@some_query))

    I need to get the results of that query back into a program variable @variable. How do I do that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •