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?