Reference Column Name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reference Column Name

Author

Topic

emzzz
Starting Member

15 Posts

Posted - 2007-11-13 : 09:06:16

Hello,

I want to be able to reference a column name that is provided by using the Information_Scheme.Columns. I have inserted the columns names of TableA into a temp table. I then use a while loop to pull out the name of each column to reference in a select statement. It is at that point that I am stuck. The column name from my temp table does not work in the same way as it would if I typed it manually.

This is my code:

INSERT INTO #Temp (Field)
SELECT
C.COLUMN_NAME
FROM
INFORMATION_SCHEMA.Columns C
WHERE
TABLE_NAME = 'TableA'

--------------------------------------------

declare @count as int;
set @count = 1;
while @count <= (select count(*) from #Temp)
begin
select
(select Field from #Temp where tempId = @count) as name,
count(*)
from TableA
where
(select Field from #Temp where tempId = @count) is null;

set @count = @count + 1;
end

-----------------------------------------------

The first select is providing me with the column name. The second select is using it as a reference.

I am expecting to see: select count(*) from TableA where fieldA is null

Many thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-13 : 09:36:51

You have to use Dynamic SQL to achieve what you want.

Read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

emzzz
Starting Member

15 Posts

Posted - 2007-11-13 : 10:48:37

Many thanks! I was using Exec instead of EXEC sp_executesql when I attempted my dymanic sql earlier which is why I thought I couldn't reference the column name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources