sql - Select the just-inserted record with a composite Primary Key -

i have table composite primary key, arranged this:

create table [dbo].[mytable] (     [some_id] [smallint] not null,     [order_seq] [smallint] not null,     -- etc... ) 

both of these columns part of primary key (it's 4-part pk on real table, i've simplified sake of example). none of columns identities. i'm writing stored proc inserts new record @ next order_seq given some_id:

create procedure some_proc (     @some_id smallint,     @newseq smallint output ) begin     insert mytable (some_id, order_seq)     values      (          @some_id,           (select max(order_seq) + 1 mytable some_id = @some_id)     )      set @newseq = /* order_seq of newly-inserted row */ end 

i need know how set @newseq. i'd avoid running select query after insert, because don't want run concurrency issues -- i'm prohibited locking table or using transaction (don't ask).

as far know, can't use scope_identity() because none of columns identity. how can set newseq correctly?

first, if pk contains 4 columns, each insert must include 4 columns. second, output clause if using sql server 2005+

declare @newseqtable table( order_seq int not null )  insert mytable( some_id, order_seq, otherpkcol, otherpkcol2 ) output inserted.order_seq @newseqtable select @some_id, max( order_seq ) + 1, otherpkcol, otherpkcol2 mytable some_id = @some_id  select order_seq @newseqtable 

output clause (transact-sql)