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

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

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.