trying use a cursor to update a table

Question

Post reply

trying use a cursor to update a table

Mário Almeida

Old Hand

Points: 314
More actions
August 31, 2006 at 9:28 am

#174135

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