syntax error in Cursor - 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)

syntax error in Cursor

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-04-21 : 21:23:46

I keep getting an error says:
Server: Msg 170, Level 15, State 1,Line 14
Line 14: Incorrect syntax near '@sql'.
Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'end'

What I am doing wrong?
-------------------------------------------------------------------

declare @col varchar(10)
declare @table varchar(10)
declare @sql varchar(200)
declare cc cursor
fast_forward
for

select a.name as [column],b.name as [Table] from syscolumns a join sysobjects b on a.id = b.id where a.name = 'Pat_id'
open CC
fetch next from cc into @col,@table
while @@FETCH_sTATUS = 0
begin
@sql = '''update ''[@Table]''' 'set ''[@col]'' = rtrim(ltrim(''[@col]'')'''

fetch next from cc into @col,@table
end
close cc
deallocate cc
print @sql

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-21 : 21:30:40

It's the same problem you have here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48467

And the solution is the same as in that thread: post your table structure, and a description of what you're trying to accomplish. I can tell you now you'll be banging your head on a wall trying to get this working, and you'll only end up with a concussion.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-21 : 23:31:22

This statement:
@sql = '''update ''[@Table]''' 'set ''[@col]'' = rtrim(ltrim(''[@col]'')'''

should be changed to this:

SET @sql = 'update [' + @Table + '] set [' + @col + '] = rtrim(ltrim([' + @col + ']))'

Also, I assume you will want to execute this sql statement within the WHILE loop:

EXEC @sql

And you should make your variable lengths (varchar(10)) longer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources