如何在sql server存储过程中生成两个指定数字之间的数字？ [英] How to generate number between two specified number in sql server store procedure?

问题描述

您好b $ b我在每周一家报纸公司工作。有15000个订户，订阅未来3到4年。每年52周，我必须为每个订户生成基于周（1001 ...... 3000）的发行号，并存储到数据表中。我正在使用mssql 2012.我已经编写了Store Procedure（我在下面给出）工作正常。但是执行到服务器时需要花费很长时间半小时。如果使用aspx页面，它将停止工作。我也增加了时间。没用请检查我的商店程序并告诉我该怎么做



谢谢



Maideen



我的商店流程

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 procesure

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 

推荐答案

很可能CURSOR导致数据库锁定问题，无论如何它很慢而且不是推荐的方法。



另一种选择使用CURSOR是为了确定记录的数量并使用WHILE循环（来自的概念，以及 SQL游标 - 如何避免它们[[a href =http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx\"target =_ blank title =新窗口> ^ ]）



我为此示例生成了以下表格

It is quite possible that the CURSOR is causing database locking issues, in any event it is very slow and not a recommended approach.

An alternative to using CURSOR is to determine the number of records and use a WHILE loop (idea taken from, and further details at, SQL Cursors - how to avoid them[^])

I generated the following tables for this example

CREATE TABLE [SUB_tbl_Reports]
(
	code varchar(10),
	Pstart int,
	PEnd int
)
CREATE TABLE [SUB_tbl_Reports_Audit]
(
	code varchar(10),
	PIssueNumber int
)
insert into [SUB_tbl_Reports] values
				('A',  1, 5), 
				('B', 11, 15),
				('C', 21, 25),
				('D', 31, 35)

第一步是c创建一个临时表以包含添加了行号的基础数据

First step is to create a temporary table to contain the base data with an added Row Number

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp END
SELECT *, ROW_NUMBER() OVER (ORDER BY code) AS ROWID INTO #temp FROM [SUB_tbl_Reports]

（您可能想在表上使用现有的IDENTITY列，但如果删除任何记录，这将失败，留下身份证号码的空白。使用Row_Number确保没有间隙）



现在定义并初始化循环的循环计数器并捕获表上的行数

(You may be tempted to use an existing IDENTITY column on a table, but this will fail if any records have been deleted, leaving gaps in the ID numbers. Using Row_Number ensures that there are no gaps)

Now define and initialise the loop counter for the loop and capture the number of rows on the table

DECLARE @RowCount int
SET @RowCount = 1
DECLARE @NumRows int
SELECT @NumRows = (SELECT COUNT(*) FROM [SUB_tbl_Reports])

现在，您可以使用生成的ROWID逐步执行表格没有用SQL WHILE循环锁定整个表格。

Now you can step through the table using the generated ROWID without locking the entire table up with a SQL WHILE loop.

DECLARE @code varchar(10)
DECLARE @PStart int
DECLARE @PEnd int
WHILE @RowCount <= @NumRows
BEGIN
    SELECT @code = code, @Pstart = Pstart, @PEnd = Pend
    from #temp where ROWID = @RowCount;

     -- ... do some stuff here

    SET @RowCount = @RowCount + 1
END

在你的情况下在这里做一些事情是生成x表行，其数字从值y开始，其中x和y可以从原始表中导出。最简单的方法是使用公用表表达式（CTE）从Pstart到Pend生成一系列数字（从在SQL中生成序列 [ ^ ]）

WITH q AS
       (
       SELECT  @Pstart AS num
       UNION ALL
       SELECT num + 1
       FROM q
       WHERE num < @Pend
       )
   insert into [SUB_tbl_Reports_Audit] SELECT [#temp].code, num FROM [#temp], q WHERE ROWID = @RowCount
   OPTION (MAXRECURSION 0)

注意MAXRECURSION提示选择 - 默认值为100，但0删除任何限制。另一种方法是为您可能需要的预期最大行数选择一个合适的数字，但这必须是硬编码的 - 不能使用变量。



整个小提琴转载如下：

Note the MAXRECURSION hint on the select - default is 100 but the 0 removes any limitations. An alternative is to pick an appropriate number for the expected maximum number of rows you may need, but this will have to be hard-coded - a variable cannot be used.

The entire fiddle is reproduced below:

CREATE TABLE [SUB_tbl_Reports]
(
	code varchar(10),
	Pstart int,
	PEnd int
)

CREATE TABLE [SUB_tbl_Reports_Audit]
(
	code varchar(10),
	PIssueNumber int
)

insert into [SUB_tbl_Reports] values
				('A',  1, 15), 
				('B', 11, 15),
				('C', 21, 25),
				('D', 31, 35)


IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp END
SELECT *, ROW_NUMBER() OVER (ORDER BY code) AS ROWID INTO #temp FROM [SUB_tbl_Reports]

DECLARE @RowCount int
SET @RowCount = 1
DECLARE @NumRows int
SELECT @NumRows = (SELECT COUNT(*) FROM [SUB_tbl_Reports])

DECLARE @code varchar(10)
DECLARE @PStart int
DECLARE @PEnd int

WHILE @RowCount <= @NumRows
BEGIN
	SELECT @code = code, @Pstart = Pstart, @PEnd = Pend
	from #temp where ROWID = @RowCount;
	
	 WITH q AS
			(
			SELECT  @Pstart AS num
			UNION ALL
			SELECT num + 1
			FROM q
			WHERE num < @Pend
			)
		insert into [SUB_tbl_Reports_Audit] SELECT [#temp].code, num FROM [#temp], q WHERE ROWID = @RowCount
		OPTION (MAXRECURSION 0)

	SET @RowCount = @RowCount + 1
END

select * from [SUB_tbl_Reports_Audit]

这篇关于如何在sql server存储过程中生成两个指定数字之间的数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！