Проблема следующая:
имеется таблица
CREATE TABLE jurn (
counterID int IDENTITY (1, 1) NOT NULL ,
classID int NOT NULL ,
number int NOT NULL
... )
Необходимо добавить новую запись с заданным полем classID при этом поле number должно принять значение 1 если поля с таким classID не было и max(number)+1 если было. Другими словами поле number должно быть упорядоченно внутри поля classID и начинаться с 1 для каждого значения classID.
Я написал процедуру
CREATE PROCEDURE addRecord @classID int
AS
declare @count int
declare @number int

begin tran my
select @number = max(number), @count = count(*) from jurn with(tablockx) where classID=@classID
if @count = 0
select @number = 1
else
select @number = @number + 1
insert into jurn(classID,number) values(@classID,@number)
if @@ERROR <> 0
begin
rollback tran my
return 0
end
else
commit tran

Если убрать транзакцию и блокировку массовое добавление с разных станций приведет к дублируемым результатам. С другой стороны блокировать целую таблицу для вставки одной записи не есть гут. Может кто знает другой способ ?

Заработало так

ALTER PROCEDURE addRecord
@classID int
AS
INSERT INTO jurn (classID, number)
SELECT @classID, ISNULL(MAX(number)+1,1)
FROM jurn WITH(TABLOCK)
WHERE classID=@classID

Спасибо за идею.