Why does the results return "so such column"

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)

Why does the results return "so such column"

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-18 : 08:30:15

Mike writes "I'm trying to run this MSSQL command, with help from your site I'm almost there :-)

declare @vSQL varchar(1000),@qtr char(4), @y char (4), @q char(1),@item char(3)
set @q = datepart(qq,getdate())
set @y = right(datepart(yy,getdate()),2)
set @qtr = 'q'+@q+@y
set @item = 'IDS'
print @qtr
set @vSQL = 'update metrics_test set '+@qtr
set @vSQL = @vSQL + '= 999'
set @vSQL = @vSQL + 'where item = "+@item
execute(@vSql)

This is the results I get:
q204
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'IDS'.

the IDS column is there, really it is :-) If I run the query without the execute command and set column q204 to 999 without using a variable it works fine, but I want it to be dynamic, and only in the IDS column.

thanks guys!
Mike B"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-05-18 : 08:42:13

Can you post the DDL for the metrics_test table?

Raymond

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-18 : 08:47:35

[code]
declare @vSQL varchar(1000),@qtr char(4), @y char (4), @q char(1),@item char(3)
set @q = datepart(qq,getdate())
set @y = right(datepart(yy,getdate()),2)
set @qtr = 'q'+@q+@y
set @item = 'IDS'
print @qtr
set @vSQL = 'update metrics_test set '+@qtr
set @vSQL = @vSQL + ' = 999'
set @vSQL = @vSQL + ' where item = '+@item

select @vsql
[/code]
Returns the follwing result... Is the update correct???
[code]
update metrics_test set q204 = 999 where item = IDS
[/code]

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-18 : 08:54:04

a couple of errors I see building the string
you need a space before the where and if IDS is a value it needs single quotes to become a string literal.

set @vSQL = @vSQL +' where item = '+ '''' + @item + ''''

If IDS is not a value but a column then we'll need the DDL as Raymond requested to see what you are trying to accomplish.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources