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'
where colid = @i and id = object_id('Test')
Help needed:Pulling data from tables using Column ID
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
Re: Help needed:Pulling data from tables using Column ID
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+'<'