如何在存储过程中执行序列号 [英] How to do Sequence Number in Stored Procedure

问题描述

您好。



我在SP下面有一个问题。

我在序列中创建了DOno（MS2012）

如果执行SP，所有记录的Dono显示相同。



请给我建议



Maideen



这是SP

Hi.

I have an issue in below SP.
I have created DOno in Sequence (MS2012)
If execute SP, Dono for all record shows same.

Pls advise me

Maideen

Here is SP

ALTER Procedure [dbo].[W_usp_SO_GENERATION]
	@SoNo nvarchar(20),
	@SoDate datetime,
	@STDB nvarchar(5),
	@Products nvarchar(50),
	@issueNo nvarchar(10),
	@User nvarchar(20),
	@terminal nvarchar(50)

As
Begin

Declare @DoNo varchar(10)
SET @DoNo = next value for DONo
set @DoNo = 'DO-' + right('000000'+convert(varchar,@DoNo),6) 

INSERT INTO [dbo].[SOMaster_TEMP] (SoNo, SODate, SVCode, SVName,  MVCode, DistCode,RouteCode,Area,Edition, SeqNo,
								   Qty,Rate, STDB, BSTD, BEND, BLOS,Products, IssueNo, CreatedBy, CreatedOn, Terminal, 
								   DONo,GSTP,Type)

SELECT      @sono, @SoDate,  SVCode,  SVName,  MVCode,  DistCode, RouteCode, Area, Edition,  SeqNo,  '0', Rate, @STDB, '0', '0', '1', @Products, @issueNo, @User, @SoDate, 
	@terminal,  @DoNo, '6', type  FROM      [dbo].[vwRDMS]

推荐答案

根据您提供的代码，值为每次通话时， @DoNo 变量应该不同。



为了查看序列行为，您可以使用PRINT语句来显示变量的内容，例如：

Based on the code you provided, value for @DoNo variable should be different on each call.

For seeing the sequence behaviour you can use PRINT statement to show the content of you variable, like:

Declare @DoNo varchar(10)
SET @DoNo = next value for DoNo
PRINT @DoNo
set @DoNo = 'DO-' + right('000000'+convert(varchar,@DoNo),6) 
PRINT @DoNo

作为附注，我建议存储值从原来的顺序。如果你需要在显示时格式化它，可以在SELECT语句中进行格式化或使用计算列。



编辑：



如果插入中使用的select返回多行，则变量中的相同值将用于所有行。因此需要在行的基础上生成值。



因此请尝试按如下方式修改程序

As a side note, I would suggest storing the value from the sequence as-is. If you need to format it when displayed, either do the formatting in SELECT statements or use a computed column.



If the select used in the insert return several rows then the same value from the variable is used for all of the rows. Because of this the value need to be generated on row basis.

So try modifying the procedure as follows

ALTER Procedure [dbo].[W_usp_SO_GENERATION]
	@SoNo nvarchar(20),
	@SoDate datetime,
	@STDB nvarchar(5),
	@Products nvarchar(50),
	@issueNo nvarchar(10),
	@User nvarchar(20),
	@terminal nvarchar(50)
 
AS
BEGIN
 
INSERT INTO [dbo].[SOMaster_TEMP] 
   (SoNo, SODate, SVCode, SVName,  MVCode, DistCode,
    RouteCode,Area,Edition, SeqNo,Qty,Rate, STDB, BSTD, BEND, BLOS,
    Products, IssueNo, CreatedBy, CreatedOn, Terminal, DONo,GSTP,Type)
SELECT @sono, @SoDate,  SVCode,  SVName,  MVCode,  DistCode, RouteCode, 
       Area, Edition,  SeqNo,  '0', Rate, @STDB, '0', '0', '1', 
       @Products, @issueNo, @User, @SoDate, @terminal,  
       'DO-' + right('000000'+convert(varchar,(next value for DoNo)),6), 
       '6', type  
FROM [dbo].[vwRDMS]

这篇关于如何在存储过程中执行序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！