update record using loop not working 2

[crystaljen]

some one out there help me please

i've written a simple loop whereby
i've inserted 1 rec into a #temp table using store procedure.

Using the same rec, i attempted to update the fields
named col2, col3 but the record dont seems to update

the records in #TEMP_AGE IS
REC_TYPE is varchar, trans_age int, col1 to col8 are all varchar

Below is the update during the loop...what could be wrong here.

begin
SET @a = 2
start_loop:
BEGIN
select @colnm = 'col'+convert(varchar,@a)
begin tran @transname
update #temp_age
set @colnm = convert(varchar,(@interval * @a))
where rec_type = 'title'

commit tran @transname
set @a = @a + 1
END
if @a < 9 goto start_loop
end

 

[LakshmiKiran]

Hi,
Since dynamically column names should get updated try using a dynamic query..something as below whcih works fine..
Create table #TEMP_AGE
(
REC_TYPE varchar(50),
trans_age int,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50),
col4 varchar(50),
col5 varchar(50),
col6 varchar(50),
col7 varchar(50),
col8 varchar(50)
)
Insert into #TEMP_AGE values
('title',1,'1','2','3','4','5','6','7','8')
declare @a int
Declare @colnm varchar(60)
declare @transname varchar(100)
declare @interval int
declare @strQuery varchar(6000)

Set @strQuery = ''

begin
SET @a = 2
set @interval = @a
start_loop:
BEGIN
select @colnm = 'col'+convert(varchar,@a)
-- begin tran @transname
Set @strQuery = 'update #temp_age
set ' + @colnm + ' = convert(varchar,(' + cast(@interval as varchar) + ' * ' +
cast(@a as varchar)+ '))
where rec_type = ''title'' '

EXEC(@strQuery)

/*update #temp_age
set @colnm = convert(varchar,(@interval * @a))
where rec_type = 'title'
*/
-- commit tran @transname
set @a = @a + 1
END
if @a < 9 goto start_loop
end

select * from #TEMP_AGE
Drop table #TEMP_AGE

 

[crystaljen]

hi LakshmiKiran

wat can i say except that u make my day.


keep up the good replies

once again a BIG Thank You

 

[donutman]

This one is going into my axis of evil reference manual! Dynamic SQL to avoid typing an 8 column update...got to love the diversity of mankind. And a double star to boot!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]