Failure to use UPDATE - 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)

Failure to use UPDATE

Author

Topic

plan9
Starting Member

15 Posts

Posted - 2006-07-25 : 14:31:42

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 15:02:37

First of all, declare a variable

DECLARE @SQL VARCHAR(1000)

then set this variable to

select @sql = 'update ##CONTENC set ' + @periodo_colA + ' = ' + convert(varchar, @periodo_temp2) +
       ', ' + @periodo_colB + ' = ' + convert(varchar, @periodo_temp3) +
       ' where factura_id = ' + convert(varchar, @facturas_id2)

exec   (@sql)

instead of the two update statements you have now.

Peter Larsson
Helsingborg, Sweden

plan9
Starting Member

15 Posts

Posted - 2006-07-26 : 12:12:01

With your help I came out with the following solution that works fine

exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp2 +
', ' + @periodo_colB + ' = ' + @periodo_temp3 +
' where factura_id = ' + @facturas_id2)

thanks for your time and effort

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 12:02:42

More about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources