sql server - SQL stored procedure inserting duplicate OrderNumber -

-

I searched the Internet for the day, no effort, maybe I do not want to ask properly.

I have a SQL table like this: 

  to create table items (id int identity (1,1), order number varchar (7), item name varchar (

Then I have to create a stored procedure to put items on demand, new ORDERNUMBER : 

  Process spx_insertItems @insertNewOrderNr bit, @orderNumber varchar (7), @item name varchar (255), @n count on start count as int; If the announcement of (@insertNewOrderNr = 1) declaration @newNr = start (dbo.fun_getNewOrderNr Selection ()), enter values ​​(@newNr, @itemName, @count) in item (ORDERNUMBER, ITEMNAME calculation) @newNr in another item Enter the starting start date (ORDERNUMBER, ITEMNAME, count) values ​​(@orderNumber, @itemName, @count), SCOPE_IDENTITY () end-end
new ORDERNUMBER in the end A user-defined function is returning to the code>: 
  create function dbo.fun_getNewOrderNr () Return as varchar (7) / * This function works well * / @ Output varchar (7) Setting @currentMaxNr varchar (7) set @currentMaxNr = (isnull ((select max (ORDERNUMBER from item), 'some_default_value_here') / * value is @currentMaxNr '01 / 2014 ', the argument here is that Comes to @ newValue = '02 / 2014 'and lets set @output, so: * / set @output = @newValue return @output end

Items in the item can be inserted that are not related to any OrderNumber .

Whether an item should be new OrderNumber , this process is called with @ insertNewOrderNr = 1 , the new order number that uses That can be done to include the next item with the OrderNumber , while @ insertNewOrderNr = 0 .

Sometimes it happens that there are 2 requests for @inertNewOrderNr and there is a problem - items , which are different OrderNumbers should be consistent with the same ORDERNUMBER .

I tried to use the transaction without success.

The table structure can not be modified by me.

What will be the correct way to ensure that, when making the request together, the same newOrderNumber will not be used? I have lived here for a long time so far. Please, help

Until you use MAX (OrderNumber) you will have that problem.

You can consider using sequences:

Create Sequence 

  Make Siluex dbo.OrderNumbers Cache not increase in 1 with INST 1; Go Sequence by increase in 2014 as dbo.OrderNumberYear with no cafe increased by 1; Choose the next price for Dbo.OrderNumberYear; - Important, run this once after creation, an initial time should be returned to work correctly this year's value.

Insert code 

  Announcement @ Order number job INT = (Select Convert (INT, current_value) from sys.sequences WHERE name = 'OrderNumberYear'); If (@ Order No. Year & lt; YEAR (GETDATE ())) Start @ orderNumberYear = dbo.OrderNumberYear for next price; Restart with optional sequence dbo.OrderNumbers 1; END if (@order year year! = YEAR (GETDATE ())) Rizer (N'Order year sequence is out of sync. ', 16, 1); DECLARE @newnr varchar (15) = CONCAT (FORMAT (next value for dbo.OrderNumbers, '00 / ',' en-US '), @orderNumberYear); Include in item (order number, item name, calculation) value (@newer, @imetame, @ number) SELECT @newnr

Comments

Post a Comment

Popular posts from this blog

HTML/CSS - Automatically set height width from background image? -

-
Long time ... I do not think it's possible, but I'm shocked before! I tag anchor tags, all of which are background images, all 300px wide but their height is all the difference, is it really set to do without typing altitude? Is it to set the dimensions of BG URL? Thank you! I do not think people understand - my mistake is to hurry the question. Here is the code as an example: # ago -1 {width: 300px; Height: 410px; Background: url ('/ image-1.jpg');} # East -2 {width: 300px; Height: 420px; Background: url ('/ image-2.jpg');} # Pre-3 {width: 300px; Height: 430px; Background: url ('/ image-3.jpg');} # Pre-4 {width: 300px; Height: 440px; Background: url ('/ image-3.jpg');} & lt; A href = "#" id = "ex-1" & gt; & Lt; / A & gt; & Lt; A href = "#" id = "ex-2" & gt; & Lt; / A & gt; & Lt; A href = "#" id = "pre-3" & gt; & Lt; / A & gt...
Read more

php - Mysql Show Process - Sleep Commands and what to do -

-
I am thinking that there is something that I can do better so that my process list should start with the command with MySQL Be clear Currently I am looking for a lot: 17325 user_a localhost db_1 sleep 1132 NULL 17464 user_a localhost db_1 sleep 1124 NULL 17983 user_a localhost db_1 sleep 1078 null 18113 user_a localhost db_1 sleep 1068 NULL 18207 user_a localhost db_1 sleep 1060 NULL 18231 user_a localhost db_1 sleep 1058 NULL 18353 user_a localhost db_1 sleep 1047 NULL 18447 user_a localhost db_1 sleep 1040 null 18489 user_a localhost db_1 sleep 1036 NULL 23408 user_a localhost db_1 sleep 637 NULL Something in my phpscript is that I want to clean it Can i The script looks like: Select the ID from $ q = 'db_1 where fkId = 2'; $ Ar = mysql_query ($ q); ($ Line = mysql_fetch_array ($ qr)) {echo ($ row ['id'];} Thanks in advance! I'm guessing that when you have 10 threads, you're constantly using the connectio...
Read more

c - What is the address of buf (the local variable in the main function)? -

-
I had exercise with programming in c or classes and I followed the question: Is Buf (local variable in main function)? Either enter the answer in hexadecimal format (like 0x 8 "digits" 0-9 or A-F, 0xbfff0014) or in decimal format. Note here that we want the address of the buff, not its content. The code looks like this: int main (int argc, char * argv []) {while (1) {four buff [1024] = {0}; Int r; .... I then started the code and put the built code in the buff [1024] line and in Typed Gdb p & amp; Buf and it gives me results: 0xbffff0f0 But when I use it in this quiz it gives me the result that this value is wrong. My question is this: Is this the name of the Buff variable (P & E)? Or if not, why not? Exercise is done on the virtualbox machine, so I think everyone should be the same address There is no correct numerical answer, the address may vary from one time to another, it is a programming drill that you say the following three...
Read more