Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Create Autonumber

Posted on 2015-01-22
Medium Priority
89 Views
Hi,
I have already created Autonumber 0001 until 9999 function

My question is automated number limit is until 9999 , after that need to start from 0001

when customer enter expired date, if date have expired no need to start 0001 , need to use expired date MIN auto number alreday created.

for example after 9999 auto number , customer enter expired date 01/23/2015 , when next customer need to search any
expired date available before create auto number , if expired date in the 0010 is expired then customer automatically assigning to 0010.

See below code i wrote any idea appriciate
``````DECLARE @BarcodeNumber nvarchar(100)
DECLARE @LastBarcodeNumber INT

DECLARE @CheckAvaible int
DECLARE @BarCode bigint
DECLARE @Num1 bigint
DECLARE @BarcodeNum2 NVARCHAR(20)
DECLARE @BarcodeNum3 NVARCHAR(20)
DECLARE @ComplteBarcode nvarchar(100)
DECLARE @NumberofRecords INT
DECLARE @Results INT
select  @LastBarcodeNumber=Count(Num1) from NewBarcodes where Num1='9999'
select @CheckAvaible=Count(Num1) from NewBarcodes where Expired_BarCode is null

SELECT @NumberofRecords=Count(Num1) from NewBarcodes
SET @Results=0

PRINT @CheckAvaible

IF (@LastBarcodeNumber>=1 and @CheckAvaible=0)
BEGIN
SET @Results=1
SET @Num1=1
SET @BarcodeNum2=right('00' +'1', 2)
set @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
--SET @BarcodeNumber=right(CONVERT(nvarchar(20), @ComplteBarcode)+'0000000000000' , 12)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END

IF(@NumberofRecords=0)
BEGIN
SET @Results=1
SET @Num1=1
SET @BarcodeNum2=right('00' +'1', 2)
set @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
--SET @BarcodeNumber=right(CONVERT(nvarchar(20), @ComplteBarcode)+'0000000000000' , 12)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END

DECLARE @NotInCluded INT
SET @CheckAvaible=0
select @CheckAvaible=Count(Num1) from NewBarcodes where Expired_BarCode is Not null AND Num1 Not in('9999')
IF(@CheckAvaible>=1)
BEGIN
SET @Results=1
select  @Num1=MIN(Num1) from NewBarcodes where Num1 Not in('9999') and Expired_BarCode is Not null
SET @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'
END

IF(@Results=0)
BEGIN
SELECT  @Num1=MAX(Num1) from NewBarcodes  where  Num1 Not in('9999')
SET @Num1=@Num1+1
SET @ComplteBarcode=CONVERT(nvarchar(20),@Num1)
SET @BarcodeNumber=right('00000' +CONVERT(nvarchar(20), @ComplteBarcode), 5)+CONVERT(nvarchar(20),@BarcodeNum2)+'00000'

END

PRINT  'Max Records Number'+@BarcodeNumber +'Check Available  '+CONVERT(nvarchar(20),@CheckAvaible)

select  @CheckAlreadyCreated=Count(*) from NewBarcodes where Created_By=@Username and Expiry is NULL and Expired_BarCode is null

BEGIN
END

BEGIN
select  @BarcodeNumber=Barcode_Number from NewBarcodes where Created_By=@Username and Expiry is NULL and Expired_BarCode is null
END

UPDATE NewBarcodes
SET Expired_BarCode=NULL
WHERE Barcode_Number=@BarcodeNumber
``````
0
Question by:ukerandi
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 30

Accepted Solution

Rich Weissler earned 2000 total points
ID: 40565689
I'm sorry, I don't understand the question.  Would you restate the question please?

I'm assuming your 'autonumber' is the barcode in the code segment?

Are you attempting to find an available unused 'autonumber'?  (I.e. a 'gap' in the values which you can then fill?)  I know there are a few possible methods to find the gaps...

I'm also assuming there is a strong reason to limit the 'autonumber' to four digits?  (And if limited to four digits, since it doesn't appear to really be treated as a number... is there a restraint preventing the use of non-numeric values in the four digits?)

If there isn't a length restraint on the autonumber, is there a reason not to use an Identity or Sequence?
0

LVL 10

Author Comment

ID: 40576454
I'm assuming your 'autonumber' is the barcode in the code segment? NO

Are you attempting to find an available unused 'autonumber'?
YES
But after Date Expired then user can use that expired slot
For example ID  8115 Expired date 27/01/2015
user automatically assigned to 8115
0

LVL 30

Expert Comment

ID: 40578148
If the autonumber between 0000 and 9999 is not the barcode values, then I've gotten lost in your code.  First, if the code works for you, I wouldn't knock it ... but on the idea of giving ideas, to perhaps help in the future:

There are variables for @BarCode, and @Num1 which are numbers (bigint), while @BarcodeNumber, @BarCodeNum2, @BarcodeNum3 which one would expect to be numbers based on their names are variable length character strings.  @ComplteBarcode is also a string.

While you probably have a really good idea what each section of the code is doing, I'd comment it, so that you'll still know when you have to revise it in five years... or in case someone else has to thread through it.

Second, I'd try to be real clear what you are attempting to accomplish, in this case, why your 'autonumber' is limited to four digits.  Assuming this is a solid requirement.  If this were a new implementation, I'd be tempted to break the task into a few pieces:  First, I'd repopulate the entire table with all the possible values from 0000 to 9999, and in one field I'd have a flag (either a tinyint or binary value) of 'Available', and initially, I'd flag every entry as available.  A periodic (daily?) automated task would scan the table and mark expired slots as available.  Finally, when you need a value, I'd just do a "select top 1 from <table> where available = <available value>", and when you assign the value, set it's expiration date and availability flag.

I assume value 9999 has some special meaning... but I don't know what that is... and I don't know if the value is suppose to be unique?

Most of the rest of what I see in the code is a lot of converting from ints to strings, etc... I assume to build a full barcode string value.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month8 days, 17 hours left to enroll