December 26, 2007 at 9:48 am
Guys,
I have following scenario which I have to return next values when I pass the id of the table, no of next values.
idchecknum
________________
1600
2300
getnextcheck(1, 5) should give next 5 values 601, 602, 603, 604, 605
getnextcheck(2, 3) should give next 3 values 301, 302, 303
Is this possible with a database function
Any suggestions/inputs would help
Thanks
December 26, 2007 at 10:56 am
a scenario like you describe depends on a few assumptions that you don't mention. Do you need the values so you can insert new records? you do know that you do not need that data if the column has the identity() property on it right? then you just insert the data and let the SQL engine generate id's for you automatically.
If you still need to go down that path,then There's a couple of questions for you:
You know the table, but not the column, right? but why by ID? why not by table name?
How do you know which column to increment? you'd need to assume one of the following:
1: there is a primary key constraint on the desired column or...
2: there is an identity() property on that column or...
3: you assume the desired column/PK is the first (or ALWAYS a specific) column of the table.
Which is true in your case?
I had to do something similar, because at some point,when one application was trying to be multi-database compliant, the Borland Database Engine did not gracefully handle identity() columns, so it was all handled manually, the way you describe.
To do that, we had a clumsy lookup table and dynamic SQL...hated it,and replaced it with Identity() property columns when the developers finally realized the folly of their ways. You don't do things manually, if there is a built in way to do it. In our case, We assumed the first column of the table was the PK, with no identity, and that was the column to be used for incrementing.
before I post a solution that may or may not point you where you want to go, can you explain a bit more?
Thanks!
Lowell
December 26, 2007 at 12:09 pm
Lowell, as always, brings up some excellent points and I, too, hate these types of sequence tables. They're a real pain and if the code isn't written perfectly, you will have hundreds of deadlocks per day... can you tell I'm speaking from experience here? 😉
None the less, some folks feel compelled to use a sequence table of this nature. Let's do it right so we can avoid the deadlocks, duplicate numbers, etc, etc, and still be able to take hundreds of hits per second with little or no blocking.
Step 1 is to assume some limits because we need a magic little thing called a Tally table to pull this off without loops or other performance limiting code. A Tally table contains nothing more than a single very well indexed column of sequential numbers and it has dozens of uses, all of which help avoid loops and other nasty critters. Here's how to make one...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Obviously the "limit" I previously spoke of would be how ever many rows you have in the Tally table... 11,000 in this case.
The reason why I elect to use a Tally table for this is that, although a "variable only" loop can be made to be very fast, if you want to return the list of check numbers as a single result set, you would need to insert the values either into a Temp Table or a Table Variable. Both of those live in memory unless they get too big and then they use TempDB. The real problem is the required INSERTS... it's much cheaper to do a single Select than to do dozens of Inserts followed by the required Select to get the result set.
Here's the demo code to accomplish your task... read the comments. Note also that this is NOT possible in a UDF (whether you use my code or not) because UDF's cannot be made to Update an external table.
--===== Create the test table and preload with the posted data...
-- This is NOT part of the solution... it's just a test setup
CREATE TABLE dbo.sqCheckNumber (ID INT PRIMARY KEY CLUSTERED, CheckNum INT)
INSERT INTO dbo.sqCheckNumber
(ID, CheckNum)
SELECT 1,600 UNION ALL
SELECT 2,300
--===== Declare variables (prefix of "p" indicates what a parameter would be for a proc
DECLARE @pCheckBookID INT
DECLARE @pIncrement INT
DECLARE @NextCheck INT
SET @pCheckBookID = 1 --We need check numbers from Check Book 1
SET @pIncrement = 5 --We need 5 checks for our test
--===== This method prevents deadlocks and is the only way I know of to absolutely
-- prevent getting dupe check numbers without either a transaction or a deadlock.
UPDATE dbo.sqCheckNumber
SET @NextCheck = CheckNum = CheckNum + @pIncrement
WHERE ID = @pCheckBookID
--===== Return the check numbers
SELECT N + (@NextCheck-@pIncrement)
FROM dbo.Tally
WHERE N <= @pIncrement
--===== This just displays the content of the sequence table and is NOT
-- part of the solution
SELECT * FROM sqCheckNumber
Now... a warning... you'll probably like to turn this into a stored procedure and that's fine... BUT, under NO circumstances can you include the call to the stored procedure anywhere where a ROLLBACK is possible. To do so would cause serious problems with your sequence and THAT is one of the most dangerous aspects of using such a sequence table. You MUST do code reviews to ensure that the code is never in harms way of a ROLLBACK.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply