Trying to use a cursor to update a table

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)

Trying to use a cursor to update a table

Author

Topic

plan9
Starting Member

15 Posts

Posted - 2006-08-31 : 11:25:25

Hello

I'm trying to use a cursor and some dynamic sql to updtate a table, but I'm
having a error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'periodo5'.

the code....

--declare @jp_temp2 as int
declare @sub_linhas as int
declare @jp_colA as varchar(50)
declare @jp_count as int
declare @teste_soma_column as varchar(200)
declare @teste_soma_where as varchar(50)
declare @jperiodo as decimal(23,3)
set @jperiodo= 0
set @teste_soma_where= 'T. Juro Periodo'
--set @jp_temp2 = 1

print 'testing......'

DECLARE teste_soma_temp CURSOR FOR
SELECT column_NAME FROM INFORMATION_SCHEMA.COLUMNS where
table_name='CONTENC' and SUBSTRING(column_NAME, 1, 7) = 'periodo'

OPEN teste_soma_temp --abrir cursor

FETCH NEXT FROM teste_soma_temp INTO @teste_soma_column

WHILE @@FETCH_STATUS = 0
BEGIN
print 'testing2......'

declare @q nvarchar(200)
set @q='set @jperiodo = (select sum(' +@teste_soma_column+ ') from
CONTENC where contracto = ''' + @cont_descCursor + ''')'
exec sp_executesql @q, N'@jperiodo decimal(12,2) output', @jperiodo
output
--select @jperiodo

print
'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'
print '@teste_soma_column'
print @teste_soma_column
print
'sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss'

exec('update CONTENC set ''' + @teste_soma_column + ''' = ' +
@jperiodo +' where contracto = ''' + @cont_descCursor + ''' and tipo_doc =
' + @teste_soma_where )

FETCH NEXT FROM teste_soma_temp INTO @teste_soma_column
END

CLOSE teste_soma_temp
DEALLOCATE teste_soma_temp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-31 : 11:50:37

exec('update CONTENC set ' + @teste_soma_column + ' = ' +
@jperiodo +' where contracto = ''' + @cont_descCursor + ''' and tipo_doc =
' + @teste_soma_where )

set @teste_soma_where= 'T. Juro Periodo'
s.b. (probably)
set @teste_soma_where= 'T.Juro Periodo'

Try displaying the striungs and you will see the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources