working with columns names - 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 2000 Forums

Transact-SQL (2000)

working with columns names

Author

Topic

barton-fink
Starting Member

2 Posts

Posted - 2004-06-18 : 11:39:41

Hi,
I'd like to work with fields that have sequential names in a table
For example:

TABLE1(Field1 int, Field2 int, ..., Field10 int) ("Field1" is the real name of the first field)

and the instructions :

select @counter=1
while (...)
BEGIN
select * from TABLE1
where "Field"+@counter=yyy

select @counter=@counter+1
END

But that doesn't work!

What should I do ?
Please give me a little help...
Thanks

SmileyConspiracy
Starting Member

8 Posts

Posted - 2004-06-18 : 11:58:26

You'll need to use dynamic SQL, try something like this.

declare @counter int
declare @SQL varchar(1000)

select @counter=1
while (...)
BEGIN

set @SQL ='select * from TABLE1 where Field' + cast( @counter as varchar) + '=yyy'
exec( @SQL)

select @counter=@counter+1
END

barton-fink
Starting Member

2 Posts

Posted - 2004-06-28 : 08:07:04

thanx it would work fine

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-06-28 : 17:22:53

quote:
Originally posted by barton-fink

But that doesn't work! What should I do ?

This may not sound like friendly advice, but what you really should do is fix your schema. Why do you have fields named Field1, Field2, etc.? Why are you trying to dynamically build a SELECT statement?

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources