Select the just-inserted record with a composite Primary Key

I have a table with a composite Primary Key, arranged something like this:

CREATE TABLE [dbo].[mytable]
(
    [some_id] [smallint] NOT NULL,
    [order_seq] [smallint] NOT NULL,
    -- etc...
)

Both of these columns are part of the primary key (it's actually a 4-part PK on the real table, but I've simplified it for the sake of the example). None of the columns are identities. I'm writing a stored proc that inserts a new record at the next order_seq for a given some_id:

CREATE PROCEDURE some_proc
(
    @some_id smallint,
    @newSeq smallint OUTPUT
)
A开发者_JS百科S
BEGIN
    insert into mytable (some_id, order_seq)
    values 
    (
         @some_id, 
         (select max(order_seq) + 1 from mytable where some_id = @some_id)
    )

    set @newSeq = /* order_seq of the newly-inserted row */
END

I need to know how to set @newSeq. I'd like to avoid running a select query after the insert, because I don't want to run into concurrency issues -- I'm prohibited from locking the table or using a transaction (don't ask).

As far as I know, I can't use SCOPE_IDENTITY() because none of the columns is an identity. How can I set newSeq correctly?

---

First, if the PK contains four columns, then each insert must include all four columns. Second, you could look into the Output clause if you are using SQL Server 2005+

Declare @NewSeqTable Table( Order_Seq int not null )

Insert MyTable( some_id, order_seq, otherPkCol, otherPkCol2 )
Output inserted.order_seq Into @NewSeqTable
Select @some_id, Max( order_seq ) + 1, otherPkCol, otherPkCol2
From MyTable
Where some_id = @some_id

Select Order_Seq
From @NewSeqTable

OUTPUT Clause (Transact-SQL)

---

The answer here depends on the size/concurrency issues in your system. If you are UNSURE as to as to the concurrency issues assume access is multi-threaded.

Single Threaded

If you have small system or you can be sure that only one thread will touch this function at a time, then something like the following will work :

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int


SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

Multi-Threaded Access

If you cannot be assured that only a single thread will access the proc, then you need a transaction in your code. From what you've shared, it appears that you will need a SERIALIZABLE transaction. SERIALIZABLE is the least concurrent (and most protective) transaction available in SQL Server. Since you do a read that identifies a max you'll need serializable to prevent phantom inserts that would alter the result.

Although you would likely want error handling, a procedure like the following should work....

CREATE PROCEDURE some_proc ( @KeyPart1 smallint, @newSeq smallint OUTPUT ) 
AS

DECLARE @KeyPart1 int
DECLARE @KeyPart2 int

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SET @KeyPart1 = (SELECT <whatever your logic is here>)
SET @KeyPart2 =  select max(order_seq) + 1 from mytable where some_id = @KeyPart1

insert into mytable (some_id, order_seq)
values  ( @KeyPart1, @KeyPart2 )

set @newSeq = @KeyPart2

COMMIT TRAN

---

Unless I'm mistaken, you already have concurrency issues because of the "select max(order_seq) + 1 from mytable" statement. I'd say the problem as you posed it (being unable to lock or do transactions) isn't possible.

If order_seq weren't a smallint, I'd say generate a very large random number as your order_seq, and regenerate on the (presumably rare) insert exceptions. But that is an extreme fix for a basically unworkable situation.

The only other alternative (and I warn you it's bizarre) is to make a small dummy table that DOES have an identity column, and then basically copy that generated id into newSeq.

---

Why don't you just assign to @newSeq first, then use the @newSeq variable in the insert?