Getting a column name from a varchar

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)

Getting a column name from a varchar

Author

Topic

patterc1
Starting Member

2 Posts

Posted - 2005-11-03 : 16:13:27

My @Attr var keeps getting set to the value of @a.. I'm pretty new to t-sql and I see what is happening. I'm setting the @Attr to SELECT @a but how do I tell sql that @a is a column name?

DECLARE @sql varchar(500)
DECLARE @Attr varchar(20)
DECLARE @numAttr int
DECLARE @i int
DECLARE @size int
DECLARE @A varchar(5)

DECLARE @InvType varchar(20)
SET @InvType = 'KDFLocation'

SET @numAttr = 20
SET @i = 1
SET @size = 1
SET @Attr = ''

SET @sql = 'SELECT ls.LocationName, lc.locationstoragedefid, '

WHILE(@numAttr >= @i)
BEGIN

SET @A = 'A' + CAST(@i AS varchar(2)) + ''
SET @Attr = (SELECT (@a) FROM Inv_LocationConfigurationDef WHERE LocationConfigurationName = @InvType)
SELECT @sql = @sql + 'a' + CAST(@i AS varchar(2)) + ' as ' + @Attr

IF(@numAttr != @i)
BEGIN SELECT @sql = @sql + ', ' END
ELSE
BEGIN SELECT @sql = @sql + ' ' END

SELECT @i = @i + 1

END

anuj164
Starting Member

49 Posts

Posted - 2005-11-03 : 17:05:13

try dynamic query.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-03 : 17:15:34

Can you tell us what you're trying to accomplish....read the hint link below

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-03 : 21:16:16

I think this is what you want (I didn't check the syntax carefully though ...):

DECLARE @sql varchar(500)
DECLARE @Attr varchar(20)
DECLARE @numAttr int
DECLARE @i int
DECLARE @size int
DECLARE @A varchar(5)
declare @str nvarchar(500)

DECLARE @InvType varchar(20)
SET @InvType = 'KDFLocation'

SET @numAttr = 20
SET @i = 1
SET @size = 1
SET @Attr = ''

SET @sql = 'SELECT ls.LocationName, lc.locationstoragedefid, '

WHILE(@numAttr >= @i)
BEGIN

SET @A = 'A' + CAST(@i AS varchar(2)) + ''

SET @str = 'SELECT @Attr = ' + @A + ' FROM Inv_LocationConfigurationDef WHERE LocationConfigurationName = ''' + @InvType + ''''

EXECUTE sp_executesql @str, N'@Attr varchar(20) OUTPUT', @Attr OUTPUT

SELECT @sql = @sql + 'a' + CAST(@i AS varchar(2)) + ' as ' + @Attr

IF(@numAttr != @i)
BEGIN SELECT @sql = @sql + ', ' END
ELSE
BEGIN SELECT @sql = @sql + ' ' END

SELECT @i = @i + 1

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources