27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
[/quote]
运行之后显示全是空值[/quote]
把运行的SQL发出来[/quote]
运行存储过程结果是NULL,然后我查看代码中的两个表,也都是空表[/quote]
在上面第一点,要在表中SysSequence配置记录
insert into [dbo].[SysSequence]([AccId],[SequenceName],[ChineseName],[Prefix],[DateLength],[Width],[CurrentValue],[CreateDate],[Creator],[Updater],[UpdateDate]
,[IsDelete],[CreatorCode],[UpdaterCode],[OperDept],[RoCode])
select [AccId]=1,[SequenceName]='test',[ChineseName]='测试',[Prefix]='T',[DateLength]=6,[Width]=4,[CurrentValue]='',[CreateDate]=GETDATE(),[Creator]='',[Updater]='',[UpdateDate]=GETDATE()
,[IsDelete]=0,[CreatorCode]='',[UpdaterCode]='',[OperDept]='',[RoCode]=''
declare @rtn varchar(50)
EXEC up_Sys_GetSequenceNO_Multi 1,'test',1,@rtn out,null
select @rtn
[/quote]
我的这个原表是发货需求,而需要的效果是运单,列1是商品名,列2是本次数量,列3是商品的最小包装数,
列4是我计算的把本次发货的数量拆分成几行运单显示,商品有两种包装,一种是M,需要两个识别码,
一种为N的包装只要一个识别码,最种这个运单还有一个运单号码,运单号是8位,识别码是9位,
第一,作为运单,就会有重复打印的需求,所以我需要把生成的运行进行保存,
第二,每天可能有多次发货,随意用日期做识别码和运单号码,都要每天重置一次,从1开始,
这样可以保证全年不重复,同时方便以后查询。这些是我的需求
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
[/quote]
运行之后显示全是空值[/quote]
把运行的SQL发出来[/quote]
运行存储过程结果是NULL,然后我查看代码中的两个表,也都是空表[/quote]
在上面第一点,要在表中SysSequence配置记录
insert into [dbo].[SysSequence]([AccId],[SequenceName],[ChineseName],[Prefix],[DateLength],[Width],[CurrentValue],[CreateDate],[Creator],[Updater],[UpdateDate]
,[IsDelete],[CreatorCode],[UpdaterCode],[OperDept],[RoCode])
select [AccId]=1,[SequenceName]='test',[ChineseName]='测试',[Prefix]='T',[DateLength]=6,[Width]=4,[CurrentValue]='',[CreateDate]=GETDATE(),[Creator]='',[Updater]='',[UpdateDate]=GETDATE()
,[IsDelete]=0,[CreatorCode]='',[UpdaterCode]='',[OperDept]='',[RoCode]=''
declare @rtn varchar(50)
EXEC up_Sys_GetSequenceNO_Multi 1,'test',1,@rtn out,null
select @rtn
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
[/quote]
运行之后显示全是空值[/quote]
把运行的SQL发出来[/quote]
运行存储过程结果是NULL,然后我查看代码中的两个表,也都是空表
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
[/quote]
运行之后显示全是空值[/quote]
把运行的SQL发出来
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
[/quote]
运行之后显示全是空值
CREATE TABLE [dbo].[SysSequenceSub](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[DateStr] [varchar](50) NOT NULL,
[CurrentValue] [bigint] NOT NULL,
[Creator] [nvarchar](50) NULL,
[CreateDate] [datetime] NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequenceSub] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC,
[DateStr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysSequence] Script Date: 2019-11-15 15:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysSequence](
[AccId] [smallint] NOT NULL,
[SequenceName] [varchar](50) NOT NULL,
[ChineseName] [varchar](50) NULL,
[Prefix] [varchar](5) NOT NULL,
[DateLength] [int] NOT NULL,
[Width] [int] NOT NULL,
[CurrentValue] [varchar](50) NOT NULL,
[CreateDate] [datetime] NULL,
[Creator] [nvarchar](50) NULL,
[Updater] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL,
[IsDelete] [tinyint] NOT NULL,
[CreatorCode] [nvarchar](50) NULL,
[UpdaterCode] [nvarchar](50) NULL,
[OperDept] [nvarchar](50) NULL,
[RoCode] [varchar](50) NULL,
CONSTRAINT [PK_SysSequence] PRIMARY KEY CLUSTERED
(
[AccId] ASC,
[SequenceName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[up_Sys_GetSequenceNO_Multi]
@AccId smallint,
@SequenceName varchar(50),
@count int,
@rtn varchar(50) output,
@Width int output,
@Prefix varchar(50)=null,
@date datetime=null
AS
set nocount on;
--Declare @Prefix varchar(5)
Declare @DateLength int
--Declare @Width int
Declare @NowDate varchar(8),@CheckStr varchar(1),@CurrentValue bigint
declare @Mark varchar(50)='' --标记是否外部前缀
if @Prefix<>'' set @Mark=@Prefix
select @Prefix = case when isnull(@Prefix,'')<>'' then @Prefix else Prefix end,@DateLength = DateLength,@Width = Width,@CheckStr='Y'
from dbo.SysSequence
where SequenceName = @SequenceName and AccId=@AccId
set @rtn=''
if isnull(@CheckStr,'')='' return;
if @date is null set @date=getdate()
Set @NowDate = left(Convert(char(8),@date,12),@DateLength)
merge dbo.SysSequenceSub tg
using (select AccId=@AccId,SequenceName=@SequenceName,DateStr=@Mark+@NowDate,CurrentValue=@count,CreateDate=getdate(),IsDelete=0) su
on tg.AccId=su.AccId and tg.SequenceName=su.SequenceName and tg.DateStr=su.DateStr
when matched then
update set tg.CurrentValue=tg.CurrentValue+su.CurrentValue
,@CurrentValue=tg.CurrentValue+su.CurrentValue
when not matched then
insert (AccId,SequenceName,DateStr,CurrentValue,CreateDate,IsDelete)
values(su.AccId,su.SequenceName,su.DateStr,su.CurrentValue,su.CreateDate,su.IsDelete)
;
set @rtn= @Prefix+@NowDate+ right(Replicate('0', @Width)+cast(isnull(@CurrentValue,@count) as varchar(20)), @Width)
--创建一个计数器的表,按年流水,最小值100000000
CREATE TABLE A
(
y VARCHAR(4),
v BIGINT
)
GO
--创建存储过程按年取数
create PROCEDURE sp_getvalue(@y VARCHAR(4),@cnt int)
as
BEGIN
IF @cnt<=0
return
if not exists(select 1 from A where y=@y)
begin
insert into A values(@y,100000000)
end
DECLARE @rst TABLE(V BIGINT)
UPDATE A SET v=v+@cnt OUTPUT Deleted.v INTO @rst WHERE y=@y
;
with ct
as
(
select 1 as id,V+1 As V from @rst
union all
select ID+1,V+1 from ct where id<@cnt
)
SELECT * from ct
end
--你的拆分
--临时使用
create table #T
(
id int,
val bigint
)
insert into #T(id,val)
EXEC sp_getvalue '2019',拆分的纪录数
--按纪录号关联更新
drop table #T
if object_id('tempdb..#list') is not null drop table #list
select 列1='A',列2=35,列3=30,列4=2,列5='N' into #list union all
select 列1='B',列2=51,列3=17,列4=3,列5='N' union all
select 列1='C',列2=14,列3=14,列4=1,列5='M'
;
with list as(
select *,mark=cast(列2/列3 as int)+iif(列2%列3>0,1,0),mark2=列2%列3
from #list
)
select 列1,a.列2,a.列3,a.列4,a.列5
,列6=case when b.number+1<a.mark then 列3
when b.number+1=a.mark and mark2>0 then mark2
else 列3
end
,[列7]=case when 列5='N' then null
else '19103'+right('0000'+cast( (ROW_NUMBER()over(order by 列1,a.mark desc)-1) as varchar(4)),4)
end
,[列8]='19103'+right('0000'+cast( (ROW_NUMBER()over(order by 列1,a.mark desc)) as varchar(4)),4)
from list a
inner join master.dbo.spt_values b on b.number+1<=a.mark
where b.type='P'
ORDER BY 列1,a.mark desc
/*
列1 列2 列3 列4 列5 列6 列7 列8
---- ----------- ----------- ----------- ---- ----------- ------------- -------------
A 35 30 2 N 30 NULL 191030001
A 35 30 2 N 5 NULL 191030002
B 51 17 3 N 17 NULL 191030003
B 51 17 3 N 17 NULL 191030004
B 51 17 3 N 17 NULL 191030005
C 14 14 1 M 14 191030005 191030006
*/