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