Next Key Service for Database tables
This provides for an easy way to generate key values for tables in a database when you don’t want to use a system generated value (like an IDENTITY column). The example is for MS SQLServer 2000 but could be adapted to any database which supports stored procedures. Note that this service assumes the key value it derives does not already exist in the database table it is passed.
You need a table in your database.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cmnNextKey]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[cmnNextKey] GO CREATE TABLE [dbo].[cmnNextKey] ( [tableName] [varchar] (128) NOT NULL , [nextKey] [decimal](18, 0) NOT NULL , ) ON [PRIMARY] GO -- add primary key ALTER TABLE [dbo].[cmnNextKey] WITH NOCHECK ADD CONSTRAINT [PK_cmnNextKey] PRIMARY KEY CLUSTERED ( [tableName] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -- permissions GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[cmnNextKey] TO [public] GO
And a stored procedure.
-- stored procedure spcmn_getNextKey SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spcmn_getNextKey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[spcmn_getNextKey] GO CREATE PROCEDURE spcmn_getNextKey @isTableName varchar(128) = null -- table to get next key for ,@oiNextKey decimal(18,0) = null output -- outputs the next key ,@iiKeys decimal(18,0) = null -- number of keys to get; null = 1 AS set nocount on -- ---------------------------------------------------------- -- Procedure: spcmn_getNextKey -- Get next technical key(s) for the table name passed in. -- ---------------------------------------------------------- declare @lsErrorText varchar(255) ,@liERROR int ,@liROWCOUNT int ,@liBeginTran int -- initialize error handling select @liERROR = @@ERROR if @liERROR <> 0 goto ErrorHandler -- begin transaction IF @@TRANCOUNT = 0 begin select @liBeginTran = 1 begin transaction end -- default values select @isTableName = ltrim(rtrim(@isTableName)) ,@oiNextKey = null -- validate table name if @isTableName is null begin select @lsErrorText = 'Table name not passed' goto ErrorHandler end -- validate number of keys requested if @iiKeys <= 0 begin select @lsErrorText = 'Number of keys requested for table "' + @isTableName + '" must be greater than 0.' goto ErrorHandler end -- default number of keys requested to 1 select @iiKeys = isNull(@iiKeys, 1) -- grab a lock while getting next key select @oiNextKey = nextKey from cmnNextKey (updlock) where tableName = @isTableName select @liERROR = @@ERROR ,@liROWCOUNT = @@ROWCOUNT IF @liERROR <> 0 goto ErrorHandler if @liROWCOUNT = 0 begin select @oiNextKey = 1 -- if new table, add it insert cmnNextKey (tableName ,nextKey) values(@isTableName ,@iiKeys + 1) end else begin -- if existing table, bump the next key update cmnNextKey set nextKey = @oiNextKey + @iiKeys where tableName = @isTableName end select @liERROR = @@ERROR ,@liROWCOUNT = @@ROWCOUNT if @liROWCOUNT <> 1 or @liERROR <> 0 goto ErrorHandler if @liBeginTran is not null commit transaction return ErrorHandler: if @liBeginTran is not null rollback transaction select @lsErrorText = 'spcmn_getNextKey: ' + isNull(@lsErrorText, 'Unexpected Error Occurred!') raiserror(@lsErrorText, 0, 1) WITH SETERROR GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[spcmn_getNextKey] TO [public] GO
Examples on the usage of next key procedure (run in ISQL session).
set nocount on go -- ---------------------------- -- getting next key for "Table" -- ---------------------------- declare @nextKey int exec spcmn_getNextKey "Table", @nextKey output select @nextKey "Get Next Key" go -- -------------------------------- -- getting next 10 keys for "Table" -- -------------------------------- declare @nextKey int exec spcmn_getNextKey "Table", @nextKey output, 10 select @nextKey "Get Next 10 Keys" go -- ------------------------------------------- -- getting multiple keys and applying to table -- ------------------------------------------- declare @nextKey int declare @keyCount int -- create and populate a temp table create table #test (keyColumn int null) insert #test (keyColumn) values (null) insert #test (keyColumn) values (null) insert #test (keyColumn) values (null) insert #test (keyColumn) values (null) -- get the count select @keyCount = count(*) from #test -- getting multiple keys for "Table" exec spcmn_getNextKey "Table", @nextKey output, @keyCount select @nextKey "Next Key" -- must subtract 1 before update select @nextKey = @nextKey - 1 -- update each row with a key update #test set keyColumn = @nextKey ,@nextKey = @nextKey + 1 -- see the results select * from #test drop table #test go set nocount off go
Thanks to A.J. Schroeder.