CREATE PROCEDURE [dbo].[Select_NO]
(
@UserId char(12)
)
AS
SET NOCOUNT ON
declare @maxNO char(6),
@newNo int,
@yearMonth char(6),
@resultNo char(12)
--取当前年月YYYYMM
select @yearMonth = convert(nvarchar(6),getdate(),112) --'YYYYMM’
if not exists(select autoid from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth and UserID = @UserId)
begin
select @resultNo = @yearMonth + '000001'
end
else
begin
--取本月最大值
select @maxNO = substring(MAX(ordNO),7,6) from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth and UserID = @UserId
--本月最大值加1
select @newno = convert(int,@maxNO) + 1
select @resultNo = @yearMonth + SUBSTRING('000000',1,6-len(@newno)) + CONVERT(varchar(6),@newno)
end
select @resultNo
if @@error > 0
return @@error
else
return -1
(
@UserId char(12)
)
AS
SET NOCOUNT ON
declare @maxNO char(6),
@newNo int,
@yearMonth char(6),
@resultNo char(12)
--取当前年月YYYYMM
select @yearMonth = convert(nvarchar(6),getdate(),112) --'YYYYMM’
if not exists(select autoid from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth and UserID = @UserId)
begin
select @resultNo = @yearMonth + '000001'
end
else
begin
--取本月最大值
select @maxNO = substring(MAX(ordNO),7,6) from Orders where convert(nvarchar(6),OrdDate,112) = @yearMonth and UserID = @UserId
--本月最大值加1
select @newno = convert(int,@maxNO) + 1
select @resultNo = @yearMonth + SUBSTRING('000000',1,6-len(@newno)) + CONVERT(varchar(6),@newno)
end
select @resultNo
if @@error > 0
return @@error
else
return -1