Mark Hollander
3/28/2007 4:02:00 PM
Hi All,
I hope I am posting this in the correct area. Here is, I believe, a helpfull
tool for those of us that need a table that allows us to create incremental
variables on the fly without having to restructure a table every time we need
to add an incremental variable/column/counter
E.g.
InvNo for Invoice Numbers
QuoteNo for Quotation Numbers, etc
Many thanks to Uri Dimant and Alejandro Mesa who helped put me on the right
track with their constructive criticism of the code.
Usage:
Exec GetNextId '<NameOfVariable>'
Parameters:
<NameOfVariable> Name of the variable/Counter you would like to
retrieve, the variable name length cannot exceed 30 Characters, but you can
modify the code to increase this size.
Returns:
Next Incremented Number
---- START OF STORED PROCEDURE CODE ------
DROP PROCEDURE GetNextID
GO
CREATE PROCEDURE GetNextID
( -- Start Parameter Declaration
@vcIDName as VARCHAR(30) -- The name of the variable to extract the
number from
) -- End Parameter Declaration
AS
BEGIN -- Variable Declarations start
DECLARE @nNextID Numeric(18,0)
DECLARE @iError Integer
SET @nNextID = 0 -- Initialise the variable to it's default value
END -- Variable Declarations end
BEGIN
IF EXISTS(SELECT COUNTER FROM tblCounters WHERE [NAME] = @vcIDName) --
Check to see if the Variable Name exists in the table
BEGIN
SET @iError = @@ERROR -- Store the value of @@ERROR variable
into a variable we know won't change when a command is executed
IF @iError <> 0 -- Make sure that no errors where encountered
GOTO ErrorCondition
ELSE -- Nope, No Errors here
BEGIN
-- Update the counter by a value of one
UPDATE tblCounters SET @nNextID = COUNTER = COUNTER + 1
WHERE [NAME] = @vcIDName
SET @iError = @@ERROR -- Store the value of @@ERROR
variable into a variable we know won't change when a command is executed
IF @iError <> 0 -- Make sure that no errors where
encountered
GOTO ErrorCondition -- An error occured, so goto the
section that will return the Error Code
ELSE
GOTO NoErrorCondition -- No Error was found, so goto
the section that will return the last number found in the table for the
request variable name
END
END
ELSE
/*
The variable name was not found, or an Error may have occured
*/
BEGIN
SET @iError = @@ERROR -- Store the value of @@ERROR variable
into a variable we know won't change when a command is executed
IF @iError <> 0 -- Make sure that no errors where encountered
GOTO ErrorCondition -- An error occured, so goto the section
that will return the Error Code
ELSE -- Nope, No Errors here
BEGIN
INSERT INTO tblCounters ([NAME],[COUNTER]) VALUES
(@vcIDName,1) -- Add the variable name and store a default value of one
SET @iError = @@ERROR -- Store the value of @@ERROR
variable into a variable we know won't change when a command is executed
SET @nNextID = 1 -- Set the return value to one as we did
not find the variable name in the table
IF @iError <> 0 -- Make sure that no errors where
encountered
GOTO ErrorCondition -- An error occured, so goto the
section that will return the Error Code
ELSE
GOTO NoErrorCondition -- No Error was found, so goto
the section that will return the last number found in the table for the
request variable name
END
END
END
ErrorCondition:
SELECT @iError as 'ERROR'
GOTO ExitProcedure
NoErrorCondition:
SELECT @nNextID as 'RETVAL'
GOTO ExitProcedure
ExitProcedure:
GO
---- END OF STORED PROCEDURE CODE ------
---- START OF TABLE CREATION CODE ------
CREATE TABLE [tblCounters] (
[Name] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[Counter] [numeric](18, 0) NOT NULL ,
CONSTRAINT [PK_tblCounters] PRIMARY KEY CLUSTERED
(
[Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---- END OF TABLE CREATION CODE ------
--
Thank you
Mark Hollander