quote:
jxflagg, could you show me how to use temp table and update lock?
Not a temp table, a real table. Like this:
CREATE TABLE [Exp_UniqueKeys] (
[Table_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_key] [int] NOT NULL CONSTRAINT [DF__Exp_Uniqu__last___515009E6] DEFAULT (1),
CONSTRAINT [PK_UniqueKeys] PRIMARY KEY NONCLUSTERED
(
[Table_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
With this stored procedure:
CREATE PROCEDURE NextKey(
@p_tablename varchar(128) = null,
@p_howmany int =1,
@p_nextkey int output
)
AS
begin
if @p_tablename is null
begin
raiserror (' @p_tablename is required', 16,1)
return -1
end
UPDATE exp_UniqueKeys
SET last_key = last_key + @p_howmany
WHERE table_name = @p_tablename
if @@rowcount > 0
Begin
SELECT @p_nextkey = last_key
FROM exp_UniqueKeys
WHERE table_name = @p_tablename
end
else
begin
INSERT INTO exp_UniqueKeys (
table_name,
last_key
)
values (
@p_tablename,
@p_howmany
)
if @@error != 0
begin
raiserror ('the insert statement failed', 16, 1)
return -1
end
SELECT @p_nextkey = @p_howmany
end
return 0
End
Call the above stored procedure from all of your other procedures as follows:
EXEC NextKey @p_tablename = 'Exp_Expense', @p_nextkey = @ID output
The calling procedure should be in a transaction bracket.