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: 344
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

For Update better use set based t-sql and avoid cursor.

Cursors are very expensive in terms of performance.

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Mário Almeida Old Hand Points: 344 More actions · Answer 2

What do you suggest in the present case?

kind Regards

Mário Almeida

Out For Justice SSCrazy Points: 2733 More actions · Answer 3

In your code, the variable @cont_descCursor is undefined. Perhaps that is the source of your problem.

Mário Almeida Old Hand Points: 344 More actions · Answer 4

I´ve checked and over checked that unfortunatly its not the problem.....

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

Can you please explain what you are trying to do?

Are you trying to do this:

Update CONTENC set periodo =periodo+ periodo1+periodo2???

If you can answer this I will be able to help u(avoiding the cursor)

Thanks

Sreejith