asked on

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

maheshneel

ASKER

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

knucklesbamm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imran_fast

Your procedure is correct right now what is the problem you are facing
Is it count??