trying to UPDATE a table

Question

trying to UPDATE a table

Old Hand

Points: 344

July 25, 2006 at 12:50 pm

Hello I'm trying to update dynamically a few columns of a table, dynamically because I don't know in the beggining  the number and name of the columns I try to serialize the name. but it isn't working I only get a bunch of nulls, can anyone help me. Thanks for your time and effort.declare @periodo_inicio as datetimedeclare @periodo_fim as datetimedeclare @periodo_temp as intdeclare @periodo_temp2 as decimal(12,2)declare @periodo_temp3 as intdeclare @periodo_colA as varchar(20)declare @periodo_colB as varchar(20)set @periodo_temp = 1DECLARE periodo CURSOR FORselect distinct * from #PERIODOS order by inicioOPEN periodoFETCH NEXT FROM periodo  INTO @periodo_inicio, @periodo_fimWHILE @@FETCH_STATUS = 0BEGINset @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and  @periodo_fim)set @periodo_temp3 =(select count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and  @periodo_fim)set @periodo_colA = 'periodo' + CONVERT(varchar(10),@periodo_temp)set @periodo_colB = 'dias' + CONVERT(varchar(10),@periodo_temp)update ##CONTENC set @periodo_colA = @periodo_temp2 where factura_id = @facturas_id2update ##CONTENC set @periodo_colB= @periodo_temp3 where factura_id = @facturas_id2set @periodo_temp = @periodo_temp + 1FETCH NEXT FROM periodo INTO  @periodo_inicio, @periodo_fimENDCLOSE periodoDEALLOCATE periodo

Viewing 3 posts - 1 through 2 (of 2 total)

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

declare @periodo_inicio as datetime

declare @periodo_fim as datetime

declare @periodo_temp as int

declare @periodo_temp2 as decimal(12,2)

declare @periodo_temp3 as int

declare @periodo_colA as varchar(20)

declare @periodo_colB as varchar(20)

set @periodo_temp = 1

DECLARE periodo CURSOR FOR

SELECT DISTINCT *

FROM #PERIODOS

ORDER BY inicio

OPEN periodo

FETCH NEXT FROM periodo

INTO @periodo_inicio, @periodo_fim

WHILE @@FETCH_STATUS = 0

BEGIN

SET @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

SET @periodo_temp3 = (select count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

SET @periodo_colA = 'periodo' + CONVERT(varchar(10),@periodo_temp)

SET @periodo_colB = 'dias' + CONVERT(varchar(10),@periodo_temp)

UPDATE ##CONTENC

SET @periodo_colA = @periodo_temp2

WHERE factura_id = @facturas_id2

UPDATE ##CONTENC

SET @periodo_colB= @periodo_temp3

WHERE factura_id = @facturas_id2

SET @periodo_temp = @periodo_temp + 1

FETCH NEXT FROM periodo

INTO @periodo_inicio, @periodo_fim

END

CLOSE periodo

DEALLOCATE periodo

you shoud post the DDL, sample data and desired result...

Your code just doesn't make too much sense:

even if you use a cursor you want to update the same record WHERE factura_id = @facturas_id2

Kindest Regards,

Vasc

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

the solution that I came up and that works fine

exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp2 +

', ' + @periodo_colB + ' = ' + @periodo_temp3 +

' where factura_id = ' + @facturas_id2)