#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    11
    Rep Power
    0

    How to generate number between two specified number in sql server store procedure?


    Hi
    I am working in weekly Newspaper company. There are 15000 subscriber, subscribed for next 3 to 4 years.Per year 52 weeks and I have to generate issue number based on week(1001......3000) for each subscriber and store into data table. I am using mssql 2012. I have written the Store Procedure (I have given below) working fine. But it takes long time more then half an hour when execute into server. If using aspx pages, it stop working. I have also increased time out. No Use. Pls check my store procedure and advise me how to do

    thank you

    Maideen

    My Store procedure

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER Procedure [dbo].[WR_SUB_usp_Reports_Audit] 
    As
    Begin 
    TRUNCATE TABLE [dbo].[SUB_tbl_Reports_Audit]
    
    
        Declare @code varchar(50), @start int , @end int, @start1 int, @end1 int, @str varchar(3000),@Name varchar(150), @PKcode varchar(20)
    	Declare @CurrCode varchar(5), @PaidAmt decimal, @PKAmount decimal , @PStart date, @PEnd date, @PExt date 
    	Declare @DStart date,@DEnd date, @DExt date, @ACRefNo varchar(10), @NoIssue int, @AmtCharged decimal, @AmtFull decimal
    	Declare @TEMPAmt decimal, @TEMDAmt decimal, @TESDAmt decimal, @ERDAmt decimal, @TEMPRate decimal, @TEMDRate decimal
    	Declare @TEMPCopy decimal, @TEMDCopy Decimal, @TESDCopy int,@ERDCopy  int, @PStartIssueNo int, @PEndIssueNo int 
    	Declare @DStartIssueNo int,@DEndIssueNo int
    
    	DECLARE num_cursor CURSOR FOR 
    	
    	Select Code, Name,CurrCode,PaidAmt,PKAMOUNT, PStart, PEnd, PExt, DStart,   DEnd, DExt, ACRefNo, NoIssue, PStartIssueNo,PendIssueNo,DStartIssueNo,DEndIssueNo from [dbo].[SUB_tbl_Reports] 
    	    
    	OPEN num_cursor
    
        FETCH NEXT FROM num_cursor 
        INTO @code,@Name,@PKCode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,  @DEnd, @DExt, @ACRefNo, @NoIssue, @start,@end,@start1,@end1
        WHILE @@FETCH_STATUS = 0
        BEGIN
    
            While @start <= @end  or @start1 <= @end1
            Begin
                Insert into SUB_tbl_Reports_audit (code,NAME,PKCode,Currcode,PaidAmt, PKAmount, PStart, PEnd, PExt, DStart,  DEnd, DExt, ACRefNo, NoIssue, pstartissueno,DStartIssueNo) 
    			Values (@code,@nAME,@PKCode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,  @DEnd, @DExt, @ACRefNo, @NoIssue,@start,@start1)
                Set @start= @start + 1
                Set @start1 = @start1+1
            End 
    
           FETCH NEXT FROM num_cursor 
           INTO @code,@name,@pkcode,@CurrCode,@PaidAmt, @PKAmount, @PStart, @PEnd, @PExt, @DStart,   @DEnd, @DExt, @ACRefNo, @NoIssue, @start,@end, @start1,@end1
    
        END
       
        CLOSE num_cursor
        DEALLOCATE num_cursor
    
    End
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    123
    Rep Power
    14
    Yes, it should run long time because of cursor.
    try do not use cursor
    try code below with 15000 records it should be around 1 second
    Code:
    ;with c as
    (
    select ROW_NUMBER() over(order by Name) as r,
    		Code, 
    		Name,
    		CurrCode,
    		PaidAmt,
    		PKAMOUNT, 
    		PStart, 
    		PEnd, 
    		PExt, 
    		DStart,   
    		DEnd, 
    		DExt, 
    		ACRefNo, 
    		NoIssue, 
    		PStartIssueNo,
    		PendIssueNo,
    		DStartIssueNo,
    		DEndIssueNo 
    		from [dbo].[SUB_tbl_Reports] 
    )
    insert into 
    		Insert into SUB_tbl_Reports_audit 
    		(code,
    		NAME,
    		PKCode,
    		Currcode,
    		PaidAmt, 
    		PKAmount, 
    		PStart, 
    		PEnd, 
    		PExt, 
    		DStart,  
    		DEnd, 
    		DExt, 
    		ACRefNo, 
    		NoIssue, 
    		pstartissueno,
    		DStartIssueNo) 
     select code,
    		NAME,
    		PKCode,
    		Currcode,
    		PaidAmt, 
    		PKAmount, 
    		PStart, 
    		PEnd, 
    		PExt, 
    		DStart,  
    		DEnd, 
    		DExt, 
    		ACRefNo, 
    		NoIssue, 
    		PStartIssueNo + r,
    		DStartIssueNo + r
     from c

IMN logo majestic logo threadwatch logo seochat tools logo