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
No comments:
Post a Comment