Need help with cursor for stored procedure in sql server 2000 ..please asap

This is the code for stroed procedure what i wrote..
This will fetch a set of ids from a table say table1
it will then loop through all the ids and compare each id with accountid field in other tables like table2 , table3 and grab all the id fields from table2 and table3 . and then insert those entries. tell me what is wrong in ths stored procedure
please...
maheshneelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maheshneelAuthor Commented:
code is CREATE PROCEDURE updateTerritoryRealignment
@OldOwnerId varchar(17),
@NewOwnerId varchar(80),
@transactionid int
 AS  
declare @count int
set @count = 0
declare @accountid varchar(18)
declare @ownerid varchar(18)
print @OldOwnerId
declare accountList CURSOR read_only  For
select Id ,NewOwnerId from SFDCAccountOwnerUpdate where OldOwnerID=@OldOwnerId and NewOwnerID=@NewOwnerId

--Openign the cursor acountlist to loop throught the account ids fetched and inserting new entries in territory-realignment tables
OPEN accountList
Fetch Next From accountList into @accountid ,@ownerid
While @@Fetch_status = 0
begin
      insert into Territory_realignment values( @accountid,'Account',@transactionid,@ownerid)
      --insert into Territory_realignment select id, 'Account', @transactionid, NewOwnerId from SFDCAccountOwnerUpdate where AccountId = @accountid       
           
      insert into Territory_realignment select id, 'Contact', @transactionid, NewOwnerId from SFDCContactOwnerUpdate where AccountId = @accountid
      insert into Territory_realignment select id, 'Event', @transactionid, NewOwnerId from SFDCEventOwnerUpdate where AccountId = @accountid
      insert into Territory_realignment select id, 'Opportunity', @transactionid, NewOwnerId from SFDCOpportunityOwnerUpdate where AccountId = @accountid
      insert into Territory_realignment select id, 'Task', @transactionid, NewOwnerId from SFDCTaskOwnerUpdate where AccountId = @accountid
      
      fetch next from accountList into @accountid,@ownerid      
      set @count  = @count + 1
      
end
close accountList
DEALLOCATE accountList

select * from territory_realignment_trans
select *,@count from territory_realignment
GO
0
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE updateTerritoryRealignment
@OldOwnerId varchar(17),
@NewOwnerId varchar(80),
@transactionid int
AS  
BEGIN
    declare @count int
    set @count = 0
    declare @accountid varchar(18)
    declare @ownerid varchar(18)
    print @OldOwnerId
    declare accountList CURSOR read_only  For
    select [Id] ,NewOwnerId from SFDCAccountOwnerUpdate
    where OldOwnerID=@OldOwnerId and NewOwnerID=@NewOwnerId
   
    --Openign the cursor acountlist to loop throught the account ids fetched and inserting new entries in territory-realignment tables
    OPEN accountList
    Fetch Next From accountList
    into @accountid ,@ownerid
    While @@Fetch_status = 0
    begin
         insert into Territory_realignment values( @accountid,'Account',@transactionid,@ownerid)
         --insert into Territory_realignment select id, 'Account', @transactionid, NewOwnerId from SFDCAccountOwnerUpdate where AccountId = @accountid      
               
         insert into Territory_realignment select [id], 'Contact', @transactionid, NewOwnerId from SFDCContactOwnerUpdate where AccountId = @accountid
         insert into Territory_realignment select [id], 'Event', @transactionid, NewOwnerId from SFDCEventOwnerUpdate where AccountId = @accountid
         insert into Territory_realignment select [id], 'Opportunity', @transactionid, NewOwnerId from SFDCOpportunityOwnerUpdate where AccountId = @accountid
         insert into Territory_realignment select [id], 'Task', @transactionid, NewOwnerId from SFDCTaskOwnerUpdate where AccountId = @accountid
         
         fetch next from accountList into @accountid,@ownerid    
         set @count  = @count + 1
     
    end
    close accountList
    DEALLOCATE accountList
   
    select * from territory_realignment_trans
    select territory_realignment.*,@count as [count] from territory_realignment
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
knucklesbammCommented:
I recommend NOT using a cursor for this operation. It doesn't look necessary... it will only hinder performance in a bad way. Below is an example of insert statements to do what you need without using a cursor.

CREATE PROCEDURE updateTerritoryRealignment
@OldOwnerId varchar(17),
@NewOwnerId varchar(80),
@transactionid int
AS  
BEGIN

declare @count int
set @count = 0

print @OldOwnerId

insert Territory_realignment
select ou.[id], 'Contact', @transactionid, ou.NewOwnerId
  from SFDCContactOwnerUpdate ou
   join SFDCAccountOwnerUpdate aou
     on ou.AccountId = aou.[id]
 where aou.oldownerid = @oldownerid
    and aou.newownerid = @newownerid

select @count = @count + @@rowcount

insert Territory_realignment
select ou.[id], 'Event', @transactionid, ou.NewOwnerId
  from SFDCEventOwnerUpdate ou
   join SFDCAccountOwnerUpdate aou
     on ou.AccountId = aou.[id]
 where aou.oldownerid = @oldownerid
    and aou.newownerid = @newownerid

select @count = @count + @@rowcount

insert Territory_realignment
select ou.[id], 'Opportunity', @transactionid, ou.NewOwnerId
  from SFDCOpportunityOwnerUpdate ou
   join SFDCAccountOwnerUpdate aou
     on ou.AccountId = aou.[id]
 where aou.oldownerid = @oldownerid
    and aou.newownerid = @newownerid

select @count = @count + @@rowcount

insert Territory_realignment
select ou.[id], 'Task', @transactionid, ou.NewOwnerId
  from SFDCTaskOwnerUpdate ou
   join SFDCAccountOwnerUpdate aou
     on ou.AccountId = aou.[id]
 where aou.oldownerid = @oldownerid
    and aou.newownerid = @newownerid

select @count = @count + @@rowcount

select * from territory_realignment_trans
select territory_realignment.*,@count as [count] from territory_realignment

END
GO
 
0
imran_fastCommented:
Your procedure is correct right now what is the problem you are facing
Is it count??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.