query error - 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)

query error

Author

Topic

coolcyber
Starting Member

7 Posts

Posted - 2006-03-10 : 12:37:40

Hi, i have the following query, but it doesn't run. can anyone help me find the error.

declare @sql1 varchar(255)
declare @i int
set @i=1
while (@i < 20)
begin
set @sql1 = 'select choice'+str(@i)+' from tbltest where questionID = 7'
exec(@sql1)
set @i = @i +1
if @i =10
break
else
continue
end

the error i get is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '3'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '4'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '6'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '7'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '8'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '9'.

coolcyber
Starting Member

7 Posts

Posted - 2006-03-10 : 12:41:05

the table has columns named 'choice1' ....... 'choice10'

so im trying to get each column value individually

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-10 : 13:18:47

set @sql1 = 'select choice'+ convert(varchar(2), @i)+' from tbltest where questionID = 7'

STR will convert to character data, but without specifying a length, it will default to a length of 10.

coolcyber
Starting Member

7 Posts

Posted - 2006-03-10 : 14:52:10

JoeNak after making the change, it give the column name but not the data.
it gives
choice1
choice2
etc till choice9

coolcyber
Starting Member

7 Posts

Posted - 2006-03-10 : 15:14:56

set @sql1 ='select choice'+convert(varchar(1), @i)+' from tblSurvey2_questions where questionID = 7' works
now how can i store the value in a variable?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources