Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Question Unanswered: Pulling data from tables using ColumnID

    My problem is I need to loop through columns in a table and refer to each column using its ColumnID, all this I can do, I can even pull back the Cloumn Name from the system tables, but I can't find a way of pulling the data from this into a variable...


    The code below will give me the data I need, but I don't know how to put the result from the exec statement into a variable?

    select @sql = 'select ' + name + ' from Test'
    from syscolumns
    where colid = @i and id = object_id('Test')
    exec(@sql)


    Please help

    thanks,

    Conan

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Pulling data from tables using ColumnID

    Hi,

    you might use
    exec sp_executesql <cmd>, <paramlist>, <parameters>

    this will allow you to get the result from some cmd like
    'select @Data='+@columname+' from '+@tablename


    have a look into bol

  3. #3
    Join Date
    Jun 2003
    Posts
    5

    Unhappy Help needed:Pulling data from tables using Column ID

    Hi,

    Unfortunately this didn't work...

    It has the result of only returning the column name again, rather than its data, have been stumped now on this little thing for a couple of days. If you can help please do, thanks,

    DECLARE @i int
    DECLARE @colname nvarchar(200)
    SET @i = 5

    --Gets the column name of the column we want using its Column ID
    select @colname = name from syscolumns where colid = @i and id = object_id('Test')

    --Why does the below merely return the column name rather than its data
    select @colname from test

    --The below ruturns the correct value, but I can't/don't know how to store it.
    DECLARE @sql nvarchar(1000)
    DECLARE @data int
    select @sql = 'select ' + @colname + ' from Test'
    EXEC sp_executesql @sql
    go

    thanks,

    Conan

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Help needed:Pulling data from tables using Column ID

    Hi,


    as i said before, you can use sp_executesql. read bol for details...
    you have to distinguish between vars and their values. your "select @colname from test" will select whatever is the value of colname, regardless the table you specify! even without a table name the cmd will show your var value.



    Code:
    declare @Table sysname, @ID int, @ColName nvarchar(200), @CMD nvarchar(1000), @ColData varchar(20)
    
    select @Table='test', @ID=5
    
    -- get column name for table / colid
    select @ColName=name from syscolumns where colid=@ID and id= object_id(@Table)
    
    -- build up cmd string for pulling data
    select @CMD='select @Param = '+@ColName+' from  '+@Table
    
    -- show what we have so far
    print '@Table = >'+@Table+'<'
    print '@COLNAME = >'+@ColName+'<'
    print '@CMD= >'+@CMD+'<'
    
    -- execute the cmd string pulling data from @colname into @ColData via @Param
    exec sp_executesql @CMD, N'@Param varchar(20) output', @Param = @ColData output
    
    -- here we are
    print '@COLDATA= >'+@COLDATA+'<'
    Last edited by msieben; 06-10-03 at 03:21.

Posting Permissions

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