?
Solved

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

Posted on 2006-04-14
6
Medium Priority
?
536 Views
Last Modified: 2012-06-21
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...
0
Comment
Question by:maheshneel
4 Comments
 

Author Comment

by:maheshneel
ID: 16456098
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16456145
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
 
LVL 2

Assisted Solution

by:knucklesbamm
knucklesbamm earned 1000 total points
ID: 16456324
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16459168
Your procedure is correct right now what is the problem you are facing
Is it count??
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question