Cursor returns always last row - Help me experts

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

SQL Server Development (2000)

Cursor returns always last row - Help me experts

Author

Topic

jayakumar_sqlteam
Starting Member

2 Posts

Posted - 2009-08-31 : 02:56:20

I am writing a query which takes the column values and convert that into temp table column header and insert the values from the join query which returns to the Cursor, then based on the condition i insert or update the temp table. But my query always update the last result set, below is code please guide if i am missing something here.!!.
---------------------------------------------

create table #db_sfa(db_setting_id int, field_control_type varchar(50));

Declare @Script as varchar(8000);
Declare @Script_prepare as varchar(8000);

set @Script_prepare = 'Alter table #db_sfa add

varchar(100);'
set @Script =''
select
@Script = @Script + Replace(@Script_prepare, '?',[db_setting_field_attribute])
from
db_setting_field_attribute
Exec(@Script)
-- select * from #db_sfa
-- drop table #db_sfa

declare @dbsettingid int
declare @attributeid int
declare @control varchar(50)
declare @value varchar(100)

declare get_values cursor for
SELECT db.db_setting_id AS DbSettingId, attribute.db_setting_field_attribute_id AS AttributeId, attribute.field_control_type AS Control,
look.db_setting_value AS Value
FROM db_setting db INNER JOIN
db_setting_field_lookup look ON db.db_setting_id = look.db_setting_id INNER JOIN
db_setting_field_attribute attribute ON look.db_setting_field_attribute_id = attribute.db_setting_field_attribute_id

open get_values
--fetch next from get_values

fetch next from get_values

while @@fetch_status = 0
fetch next from get_values into @dbsettingid, @attributeid,@control,@value

begin
if((select count(db_setting_id) from #db_sfa where db_setting_id = @dbsettingid)= 0)
begin
if @attributeid = 1
begin
insert into #db_sfa (db_setting_id,field_control_type,is_visible) values(@dbsettingid,@control,@value)
end
else if @attributeid = 2
begin
insert into #db_sfa (db_setting_id,field_control_type,is_mandatory) values(@dbsettingid,@control,@value)
end
else if @attributeid = 3
begin
insert into #db_sfa (db_setting_id,field_control_type,is_visible_to_supplier) values(@dbsettingid,@control,@value)
end
else if @attributeid = 4
begin
insert into #db_sfa (db_setting_id,field_control_type,min_value) values(@dbsettingid,@control,@value)
end
else if @attributeid = 5
begin
insert into #db_sfa (db_setting_id,field_control_type,max_value) values(@dbsettingid,@control,@value)
end
else if @attributeid = 6
begin
insert into #db_sfa (db_setting_id,field_control_type,draft) values(@dbsettingid,@control,@value)
end
else
else if @attributeid = 1
begin
update #db_sfa
set is_visible = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
else if @attributeid = 2
begin
update #db_sfa
set is_mandatory = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
else if @attributeid = 3
begin
update #db_sfa
set is_visible_to_supplier = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
else if @attributeid = 4
begin
update #db_sfa
set min_value = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
else if @attributeid = 5
begin
update #db_sfa
set max_value = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
else if @attributeid = 6
begin
update #db_sfa
set draft = @value, field_control_type = @control where db_setting_id = @dbsettingid and @attributeid = 1
end
end
end

close get_values
deallocate get_values
select * from #db_sfa
drop table #db_sfa

-------------------------------------------------

Thank you in advance.

Kumar

Try and fail, but don't fail to try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources