Next Id without Identity

I'm getting a duplicate key problem with this code. I can't use a Identity
column.

BEGIN TRANSACTION
INSERT INTO T1 (Id1, Id2, OrderNo, Data)
SELECT @Id1_p, @Id2_p, ISNULL(max(OrderNo),0) + 1, @Data
FROM T1
WHERE Id1 = @Id1_p AND Id2 = @Id2_p

SELECT @iOrderNo = max(OrderNo)
FROM T1
WHERE Id1 = @Id1_p AND Id2 = @Id2_p
COMMIT TRANSACTION


Primary Key = (Id1, Id2, OrderNo)

Peter

BEGIN TRANSACTION

SELECT @iOrderNo = ISNULL(max(OrderNo),0) + 1 FROM T1 WITH
(UPDLOCK,HOLDLOCK)
WHERE Id1 = @Id1_p AND Id2 = @Id2_p

INSERT INTO T1 (Id1, Id2, OrderNo, Data) SELECT @Id1_p, @Id2_p,
iOrderNo, @Data

COMMIT TRANSACTION
>

"Peter Ericsson" wrote in message

> I'm getting a duplicate key problem with this code. I can't use a Identity
> column.
>
> BEGIN TRANSACTION
> INSERT INTO T1 (Id1, Id2, OrderNo, Data)
> SELECT @Id1_p, @Id2_p, ISNULL(max(OrderNo),0) + 1, @Data
> FROM T1
> WHERE Id1 = @Id1_p AND Id2 = @Id2_p
>
> SELECT @iOrderNo = max(OrderNo)
> FROM T1
> WHERE Id1 = @Id1_p AND Id2 = @Id2_p
> COMMIT TRANSACTION
>
>
> Primary Key = (Id1, Id2, OrderNo)