sql server - SQL stored procedure inserting duplicate OrderNumber -

sql server - SQL stored procedure inserting duplicate OrderNumber -

i searched net days, no effort, maybe cant inquire in right way.

i have sql table this:

create table items ( id int identity(1,1), ordernumber varchar(7), itemname varchar(255), count int )

then have stored procedure inserting items, on demand creating new ordernumber:

create procedure spx_insertitems @insertnewordernr bit, @ordernumber varchar(7), @itemname varchar(255), @count int begin set nocount on; if (@insertnewordernr = 1) begin declare @newnr = (select dbo.fun_getnewordernr()) insert items (ordernumber, itemname, count) values (@newnr, @itemname, @count) select @newnr end else begin insert items (ordernumber, itemname, count) values (@ordernumber, @itemname, @count) select scope_identity() end end

finally there user defined function returning new ordernumber:

create function dbo.fun_getnewordernr () homecoming varchar(7) begin /* func works */ declare @output varchar(7) declare @currentmaxnr varchar(7) set @currentmaxnr = (isnull((select max(ordernumber) items), 'some_default_value_here') /* lets assume @currentmaxnr '01/2014', here comes logic increments @newvalue='02/2014' , sets @output, so: */ set @output = @newvalue homecoming @output end

into items can inserted items not belong ordernumber.

whether item should become new ordernumber, procedure called @insertnewordernr=1, returns new order number, can used insert next items ordernumber while @insertnewordernr=0.

occasionally there happens there come simultaneously 2 requests @insertnewordernr , there problem - items, should correspond different ordernumbers same ordernumber.

i tried utilize transaction no success.

the table construction cant modified me.

what right way ensure, there won't used same newordernumber when simultaneous requests procedure come? stuck here long time till now. please, help.

you have problem long utilize max(ordernumber).

you might consider using sequences:

create sequence

create sequence dbo.ordernumbers int start 1 increment 1 no cache; go create sequence dbo.ordernumberyear int start 2014 increment 1 no cache; select next value dbo.ordernumberyear; --important, run 1 time after creation, years value must returned 1 initial time work correctly.

insert code

declare @ordernumberyear int = (select convert(int, current_value) sys.sequences name = 'ordernumberyear'); if(@ordernumberyear < year(getdate())) begin select @ordernumberyear = next value dbo.ordernumberyear; alter sequence dbo.ordernumbers restart 1 ; end if(@ordernumberyear != year(getdate())) raiserror(n'order year sequence out of sync.', 16, 1); declare @newnr varchar(15) = concat(format(next value dbo.ordernumbers, '00/', 'en-us'), @ordernumberyear); insert items (ordernumber, itemname, count) values (@newnr, @itemname, @count) select @newnr

sql sql-server stored-procedures duplicates