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,

ConanHi,

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|||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|||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.

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