Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    99

    Unanswered: From Cursors to a simple Select

    Hi all

    There is a stored procedure that updates a "sales" table with the current "sales representative" taken from the "customers" table. I'm changing this mess of cursors into a simple update, my first approach was just to do a select (instead of update) just to verify that the rows selected were the ones that really needed to be changed. The select is very simple but is returning a cartesian product instead of just the 200+ (aprox) rows. I would appreciate if someone took a quick look at this select and see what I'm missing (I'm including the code for the cursor as well as the code for the select)

    CURSOR:

    Code:
    CREATE PROCEDURE [ReCast_Salesman] AS
    -- Recast salesman
    
    Declare @vsite_code 	varchar(2)
    Declare @vcustno    	varchar(10) 
    Declare @vsalrep            char(4)
    Declare @vcustfx            smallint
    Declare @Ssite_code 	varchar(2)
    Declare @Scustno    	varchar(10) 
    Declare @Ssalrep           char(4)
    Declare @Scustfx           smallint
    Declare @i         	int
    Declare @intcnt     	int
    Declare @cnt    		int
    Declare @vprodate 	datetime
    Declare @vpro  		varchar(20)
    Declare @vmesg  	varchar(30)
    Declare @vmesg2  	varchar(30)
    Declare @vmesg3  	varchar(30)
    Declare @UpdFlag 	varchar(1)
    Declare @Ucnt 		int
    Declare @icnt 		int
    
    
    Set @i = 0
    Set @cnt = 0	
    Set @ucnt = 0	
    Set @icnt = 0
    Set @UpdFlag = 'N' 
    
    Declare Customer_cur Cursor for
        Select site_code, cust_no, cust_sffx,  sales_rep
        From Customer
        where Active_Flag = 'A'  
        and sales_rep <> 'XXXX'
        Order by site_code, cust_no, cust_sffx
    For Read only
    
    Open Customer_Cur
    
    Fetch Next from Customer_Cur 
        into @vsite_code,  @vcustno, @vcustfx, @vsalrep
    
    While @@FETCH_STATUS = 0
      Begin
    
      Declare sales_cur  Cursor  for 
      	Select  site_code, cust_no,  cust_sffx, salesrep
    	From Sales
    	Where site_code = @vsite_code 
    	and cust_no = @vcustno 
    	and cust_sffx = @vcustfx  
    	and salesrep <> @vsalrep
    	Order by Site_code, cust_no, cust_sffx
    	For Update
    
      Open Sales_cur
    
        Begin
    
        Fetch Next 
        from sales_cur
        into @ssite_code,  @scustno,  @scustfx,   @Ssalrep
      
        While (@@fetch_status = 0)
            Begin
      
    --	If @vsalrep <> @Ssalrep     
             
    --             Begin              
                	    Update Sales  
    	    set salesrep =  @vsalrep
    	    Where current of Sales_cur
    
    	    set @ucnt =  @ucnt + 1
    --	  End
    
    	Fetch Next from sales_cur
    	into @ssite_code,  @scustno,  @scustfx,   @Ssalrep
    
            End    
    
    FetchNext:                
        deallocate sales_cur
    
        Fetch Next 
        from Customer_Cur 
        into @vsite_code,  @vcustno,  @vcustfx,  @vsalrep
    
        End
    
      End
    	   
    set    @vprodate = getdate()
    set    @vpro =  'Recast Salesman ' 
    set   @vmesg =  Str(@ucnt) +   '  Records Changed'   
    
    Insert into Batch
               (batchdate, process, message )
               Values (@vprodate,  @vpro, @vmesg )
      
    Close Customer_Cur
    deallocate Customer_Cur
    SELECT:

    Code:
    use salesdatamart;
    Select  A.site_code, 
    	A.cust_no,  
    	A.cust_sffx, 
    	A.salesrep,
    From Sales As A
    Inner Join Customer As B
    On	A.site_code = B.site_code AND
    	A.cust_no   = B.cust_no   AND
    	A.cust_sffx = B.cust_sffx AND
    	A.salesrep  != B.sales_rep AND
    	B.sales_rep != 'XXXX' AND
    	B.active_flag = 'A'
    Thanks in advance for your help

    Luis Torres

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    DDL and sample data?
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •