Calculating and allocating serial numbers
-
Hi, I am rather new to all this SQL Server stuff.
I need to know if what I want to do is possible or not before I spend lots of time trying to do it.
My situation is that I have a number of users that need to get a unique serial number from a central database. Without going into too much detail, there is a complicated calculation needed to create the number based on the date and the previous record.
I need the stored function to be passed a string (from the user), read the last record from a table that begins with the string, perform a calculation based on the record to generate a new value, store that new value and also pass it back to the user who requested it.
I only want one user at the time being able to do this so that I don't duplicate records.
I currently do this with a VB6 application and Microsoft Access and created a class that can only be created once. The user application sits in a loop until it is free. It works fine but now Iam upgrading the application to vb.net and SQL server.
Is what I want feasible?
Thanks in advance.
:w00t:
-
You should be able to do this using sp_getapplock / sp_releaseapplock. Check BOL for details.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Cheers,
I have a look at that.
-
Is there a way of doing that from withing managed code (CLR) using VB.NET. I have built a function to do all the calculations and am now looking at accessing the database to read/write the data.
-
there is a complicated calculation needed to create the number based on the date and the previous record
Joy.. instead of using SHA or a guid, lets re-invent the wheel and hope it wasn't made square.
-
IF SHA's or GUID's had been appropriate then they would have been used!
Making comments like that when knowing nothing about the requirements does not seen that bright to me either!
-
The SERIALIZABLE isolation level would prevent other users from adding a record to the table in the middle of your transaction. It would lock not just the (previous) last row that it read, but it puts a range lock on the pk index so no larger pk value could be inserted until the transaction completes. Something like this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANS
SELECT TOP 1 @seed = serialno FROM table ORDER BY pk DESC
SET @newserial = { function of @seed and GETDATE() }
INSERT INTO table (..., serialno) ..., @newserial
END TRANS
-
chris (9/25/2008)
Is there a way of doing that from withing managed code (CLR) using VB.NET. I have built a function to do all the calculations and am now looking at accessing the database to read/write the data.You should be able to incorporate the calculations in a SQLCLR procedure or function. I would recommend doing only the calculations in the CLR code, do not do any of the data access or management, just accept the previous value as a parameter and return the new value.
Do all of the data management from SQL code in a stored procedure that calls your SQLCLR routine. Scott's transaction-based approach is a good one (you want to avoid the sp_getapplock / sp_releaseapplock procedures if at all possible).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
chris (9/29/2008)
IF SHA's or GUID's had been appropriate then they would have been used!Well then, let's ask the question: what are the features of your serial number generator that you require for your app that cannot be achieved using the SQL Server builtin facilities?
I ask because letting SQL Server do this for you is almost always:
-- Faster running (much!)
-- More Reliable
-- Easier
-- Cheaper
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
If it's gotta be... correctly update "sequence" table would probably fit the bill here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Many thanks, I'll give the stored procedure and separate function a go. As I said before, this is my first attempt at anything on an SQL server, most of my work involves test systems used in the manufacturing industry. I am upgrading a standalone system that currently uses an Access database to store the data.
With regards to the calculation, we require a 4 digit Alpha numeric code that contains the date, a character indicating a product range, and a checksum. The checksum is calculated using ASCII character codes. The code must be able to be decoded again and validated. Added to that is a base 22 serialised portion of 3 alpha numeric characters giving 7 characters in all. (Also, some characters such as 0, O and Q are not used to avoid misinterpretation when read manually.) The current format has been in use for 9 years and cannot change as we have several applications around the world that use it.
I am sure some clever sod will be able to do this without using CLR but I have not the time to investigate how and as the VB code already exists could not justify the time either. Performance is unlikely to be an issue with only 35 users on the system.
-
I agree... if the VB code works and there is no impact on performance, there's no reason to change horses right now.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree also. Common code reuse is one of the few things that I support using SQLCLR for.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply