July 4, 2017 at 10:56 am
CNCB 17 1
CNPO 17 1
CNCP 17 3
PRT 17 5
POD 17 7
SCT 17 8
EMAIL 17 16
CSP 17 20
BST 17 35
ADJ 17 155
I have a table with three columns document prefix,year in simple format and document last number.I want a stored procedure to generate document numbers as follows: CNCB17000001,CNPO17000001,CNCP17000003,ADJ1700155 ETC. For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004 and ADJ1700156. How best can achieve this?
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
July 4, 2017 at 11:08 am
"For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"
???..."next numbers generated?"....from where/what/which process?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 4, 2017 at 11:13 am
J Livingston SQL - Tuesday, July 4, 2017 11:08 AM"For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"???..."next numbers generated?"....from where/what/which process?
word to the wise.....a little bit of effort on your part will pay dividends in the replies you get
eg:
CREATE TABLE yourtable(
prefix VARCHAR(5) NOT NULL
,Yr INTEGER NOT NULL
,docno INTEGER NOT NULL
);
INSERT INTO yourtable(prefix,Yr,docno) VALUES
('CNCB',17,1),('CNPO',17,1),('CNCP',17,3),('PRT',17,5)
,('POD',17,7),('SCT',17,8),('EMAIL',17,16),('CSP',17,20)
,('BST',17,35),('ADJ',17,155);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 4, 2017 at 11:20 am
J Livingston SQL - Tuesday, July 4, 2017 11:13 AMJ Livingston SQL - Tuesday, July 4, 2017 11:08 AM"For the examples above the next numbers generated should be CNCB17000002,CNPO17000002,CNCP17000004"???..."next numbers generated?"....from where/what/which process?
word to the wise.....a little bit of effort on your part will pay dividends in the replies you get
eg:
CREATE TABLE yourtable(
prefix VARCHAR(5) NOT NULL
,Yr INTEGER NOT NULL
,docno INTEGER NOT NULL
);
INSERT INTO yourtable(prefix,Yr,docno) VALUES
('CNCB',17,1),('CNPO',17,1),('CNCP',17,3),('PRT',17,5)
,('POD',17,7),('SCT',17,8),('EMAIL',17,16),('CSP',17,20)
,('BST',17,35),('ADJ',17,155);
public class DocNumber {
public static String getDocCounter(String doc) {
String co = "";
try {
int lastNumber = 0;
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy");
String docYear = simpleDateFormat.format(date);
ArrayList<Map> results = DBConnection.sqlQuery("SELECT lastNumber FROM doccount WITH (NOLOCK) WHERE doc = '"
+ doc + "' AND docYear = '" + docYear + "' ");
for (Map rst : results) {
lastNumber = Integer.parseInt(rst.get("lastNumber").toString());
}
if (lastNumber == 0) {
lastNumber++;
DBConnection.sqlUpdate(
"INSERT INTO doccount (lastNumber, doc, docYear) VALUES (1, '" + doc + "', '" + docYear + "')");
} else {
lastNumber++;
DBConnection.sqlUpdate("UPDATE doccount SET lastNumber = '" + lastNumber + "' WHERE doc = '" + doc
+ "' AND docYear = '" + docYear + "' ");
}
co = "00000000" + lastNumber;
co = new StringBuffer(co).reverse().toString();
co = co.substring(0, 7);
co = new StringBuffer(co).reverse().toString();
co = doc + docYear + co;
} catch (Exception e) {
e.printStackTrace();
}
return co;
}
I am trying to convert that code into a stored procedure: Year and DocPrefix are the primary keys and generation of numbers should continue.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
July 4, 2017 at 12:27 pm
Firstly, you will need a table to track the last DocNum usedCREATE TABLE dbo.DocumentNumbers (
Prefix VARCHAR(5) NOT NULL
, Yr TINYINT NOT NULL
, LastDocNo INT NOT NULL
, PRIMARY KEY CLUSTERED (Yr, Prefix)
);
Next, you will need a proc that can generate the next Document NumberCREATE PROCEDURE dbo.GetNextDocNum
@Prefix VARCHAR(5)
, @Yr TINYINT
, @DocNum VARCHAR(20) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NextDocNo INT;
INSERT INTO dbo.DocumentNumbers ( Prefix, Yr, LastDocNo )
SELECT @Prefix, @Yr, 0
WHERE NOT EXISTS (SELECT 1 FROM dbo.DocumentNumbers AS dn
WHERE dn.Prefix = @Prefix
AND dn.Yr = @Yr);
UPDATE dbo.DocumentNumbers
SET @NextDocNo = LastDocNo = LastDocNo +1
WHERE Prefix = @Prefix
AND Yr = @Yr;
SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)
END;
Finally, you will need to execute your proc to get the values.DECLARE @DocNum VARCHAR(20);
EXEC dbo.GetNextDocNum
@Prefix = 'CNCB'
, @Yr = 17
, @DocNum = @DocNum OUTPUT;
SELECT DocNum = @DocNum;
July 4, 2017 at 1:13 pm
Can we assume that there is no possibility whatsoever that the proc will be called twice (or more) at the same time?
If not, you need to add some code to deal with that possibility.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 5, 2017 at 12:29 am
If concurrency is an issue, you can also dynamically spin up SEQUENCE objects to get the next DocNumber.
There is no need for the tracking table. Just a procCREATE PROCEDURE dbo.GetNextDocNum
@Prefix VARCHAR(5)
, @Yr TINYINT
, @DocNum VARCHAR(20) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @soName SYSNAME;
DECLARE @SqlCmd NVARCHAR(1000);
DECLARE @NextDocNo INT;
SET @soName = '[dbo].[so' + @Prefix + CONVERT(VARCHAR(10), @Yr) + ']';
SET @SqlCmd = N'IF OBJECT_ID(''' + @soName + N''', N''SO'') IS NULL
BEGIN
-- Create the new SEQ object, with the specified seed values
CREATE SEQUENCE ' + @soName + N'
AS INT
MINVALUE 1
START WITH 1
INCREMENT BY 1
NO CYCLE;
END;';
--PRINT (@SqlCmd);
EXEC sys.sp_executesql @SqlCmd;
SET @SqlCmd = N'SET @NextDocNo = NEXT VALUE FOR ' + @soName + N';';
--PRINT (@SqlCmd);
EXEC sys.sp_executesql @stmt = @SqlCmd
, @params = N'@NextDocNo INT OUTPUT'
, @NextDocNo = @NextDocNo OUTPUT;
SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)
END;
And call it as beforeDECLARE @DocNum VARCHAR(20);
EXEC dbo.GetNextDocNum
@Prefix = 'CNCB'
, @Yr = 17
, @DocNum = @DocNum OUTPUT;
SELECT DocNum = @DocNum;
July 5, 2017 at 1:44 am
DesNorton - Tuesday, July 4, 2017 12:27 PMFirstly, you will need a table to track the last DocNum usedCREATE TABLE dbo.DocumentNumbers (
Prefix VARCHAR(5) NOT NULL
, Yr TINYINT NOT NULL
, LastDocNo INT NOT NULL
, PRIMARY KEY CLUSTERED (Yr, Prefix)
);Next, you will need a proc that can generate the next Document Number
CREATE PROCEDURE dbo.GetNextDocNum
@Prefix VARCHAR(5)
, @Yr TINYINT
, @DocNum VARCHAR(20) OUTPUT
AS
BEGIN
SET NOCOUNT ON;DECLARE @NextDocNo INT;
INSERT INTO dbo.DocumentNumbers ( Prefix, Yr, LastDocNo )
SELECT @Prefix, @Yr, 0
WHERE NOT EXISTS (SELECT 1 FROM dbo.DocumentNumbers AS dn
WHERE dn.Prefix = @Prefix
AND dn.Yr = @Yr);UPDATE dbo.DocumentNumbers
SET @NextDocNo = LastDocNo = LastDocNo +1
WHERE Prefix = @Prefix
AND Yr = @Yr;SET @DocNum = @Prefix + CONVERT(VARCHAR(10), @Yr) + RIGHT('00000000' + CONVERT(VARCHAR(10), @NextDocNo), 6)
END;
Finally, you will need to execute your proc to get the values.
DECLARE @DocNum VARCHAR(20);EXEC dbo.GetNextDocNum
@Prefix = 'CNCB'
, @Yr = 17
, @DocNum = @DocNum OUTPUT;SELECT DocNum = @DocNum;
This works,thank you so much!!!!!!!!!
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
July 5, 2017 at 2:50 am
Hi Des,
The approach you used was very popular amongst front-end developers, but it was condemned quite some time ago, when I was still young and hopeful.
It's a factory of deadlocks.
I prefer to stay away from UPDATE statements at any time of the day.
For last numbers I use aggregation views.
Here is the snippet to start from:
USE TEMPDB
CREATE TABLE dbo.Document (
DocType varchar(5),
DocYear tinyint,
SeqNum int,
Name nvarchar(200)
)
go
create view dbo.Document_LastNumber
as
select DocType, DocYear, MAX(SeqNum) LastNumber
from dbo.Document
group by DocType, DocYear
GO
declare @Prefix VARCHAR(5), @Yr TINYINT, @DocumentName nvarchar(200)
declare @N table (NewNumber int)
select @Prefix = 'CNCB', @Yr = 17, @DocumentName = 'First one'
insert into Document (DocType, DocYear, SeqNum, Name)
output inserted.seqNum into @N
select DT.DocType, DT.DocYear,
ISNULL(LN.lastNumber, 0) + 1, DT.DocName
FROM (
SELECT @Prefix , @Yr, @DocumentName
) DT (DocType, DocYear, DocName)
LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear
DELETE FROM @N
select @Prefix = 'CNCB', @Yr = 17, @DocumentName = 'Another one of the same type'
insert into Document (DocType, DocYear, SeqNum, Name)
output inserted.seqNum into @N
select DT.DocType, DT.DocYear,
ISNULL(LN.lastNumber, 0) + 1, DT.DocName
FROM (
SELECT @Prefix , @Yr, @DocumentName
) DT (DocType, DocYear, DocName)
LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear
SELECT * FROM @N
SELECT * FROM Document
DELETE FROM @N
select @Prefix = 'PRT', @Yr = 17, @DocumentName = 'Some one of a new type'
insert into Document (DocType, DocYear, SeqNum, Name)
output inserted.seqNum into @N
select DT.DocType, DT.DocYear,
ISNULL(LN.lastNumber, 0) + 1, DT.DocName
FROM (
SELECT @Prefix , @Yr, @DocumentName
) DT (DocType, DocYear, DocName)
LEFT JOIN dbo.Document_LastNumber LN on LN.DocType = DT.DocType and LN.DocYear = DT.DocYear
SELECT * FROM @N
SELECT * FROM Document
_____________
Code for TallyGenerator
July 5, 2017 at 3:05 am
Sergiy - Wednesday, July 5, 2017 2:50 AMHi Des,
The approach you used was very popular amongst front-end developers, but it was condemned quite some time ago, when I was still young and hopeful.
It's a factory of deadlocks.
Thanks Sergiy
July 15, 2017 at 2:11 am
I created the procedure as per advice above the only hassle is that when transactions where the procedure is called did not commit,or fail some.It will arise in some document numbers being skipped.How do I ensure that when I call my document generating procedure in other procedures,when transactions in those did not commit the previous generated number is maintained because of failure(maintain ACID properties).Just hope was clear in my explanation.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply