Search

Next Key Service for Database tables

Posted on Thursday, December 30th, 2010 at 8:20 pm in

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.

Tags:

Top