June 7, 2012 at 9:29 pm
I face duplicate problem,
I want to get NextNo value from table 'NxNo' to insert into coulmn No in table 'Orders'
for multiple user and avoid duplicate.
Table 'NxNo' keep only one record, default value in coulmn NextNo is Max value of column No from table 'Orders' +1
I want to get NextNo value and update NextNo for following case
1 Type=1 or Type=2 Update NextNo = Max(NextNo)+1
2 Type=3 Update NextNo = Max(NextNo)+2 (reserved for Type 3 and 4 , Type 3 and 4 are pair )
3 Type=4 No Need to Update NextNo because already reserved value from Type=3
I have parameter @NextOrderNo for receive No value in case Type=4,
@NextOrderNo = (No of Type=3 )+1
and parameter @increasevalue for receive how many NextNo should be added.
Here my code :
IF @NextOrderNo is null
begin
SET @OrderNo = (SELECT NextNo FROM NxNo (Nolock))
update NxNo set NextNo = NextNo @increasevalue
end
ELSE
SET @OrderNo = @NextOrderNo
INSERT INTO Orders (No, AccountNo, Product, Value, Type)
VALUES@OrderNo @AccountNo, @product, @value, @type)
Here the Table and initial Data Script
CREATE TABLE [dbo].[Orders](
[No] [int] NOT NULL,
[AccountNo] [varchar](6) NOT NULL,
[Product] [varchar](15) NOT NULL,
[Type] [tinyint] NOT NULL,
[Value] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Orders_Value] DEFAULT ((0)),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[No] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into Orders values(1, 102, 'AAA', 1, 1000.0000)
insert into Orders values(2, 111, 'BBB', 1, 5000.0000)
insert into Orders values(3, 103, 'AAA', 2, 5000.0000)
insert into Orders values(4, 101, 'AAA', 1, 5000.0000)
insert into Orders values(5, 111, 'BBB', 2, 5000.0000)
insert into Orders values(6, 102, 'BBB', 3, 5000.0000)
insert into Orders values(7, 102, 'CCC', 4, 5000.0000)
insert into Orders values(8, 111, 'AAA', 3, 2000.0000)
insert into Orders values(9, 111, 'CCC', 4, 2000.0000)
insert into Orders values(10, 103, 'BBB', 2, 15000.0000)
CREATE TABLE [dbo].[NxNo](
[NextNo] [int] NOT NULL
) ON [PRIMARY]
insert into NxNo
select max(NO)+1 from Orders
June 8, 2012 at 2:19 am
This was removed by the editor as SPAM
June 8, 2012 at 4:04 am
I have try what your suggest but unfortunately still the same result.
June 8, 2012 at 4:23 am
a couple of things here
IF @NextOrderNo is null
begin
SET @OrderNo = (SELECT NextNo FROM NxNo (Nolock))
update NxNo set NextNo = NextNo @increasevalue
end
1) remember to use the WITH statement for your nolock - hints
2) - i really don't think you should be using nolock here - you may get conflicting orders in your table if more than one person is using the system - ie duplicate order numbers
3) - you haven't explained where you are getting the duplicates - in the orders table or in the numbers table
MVDBA
June 8, 2012 at 4:26 am
Stewart "Arturius" Campbell (6/8/2012)
wertyui (6/7/2012)
I want to get NextNo value and update NextNo for following case1 Type=1 or Type=2 Update NextNo = Max(NextNo)+1
2 Type=3 Update NextNo = Max(NextNo)+2 (reserved for Type 3 and 4 , Type 3 and 4 are pair )
3 Type=4 No Need to Update NextNo because already reserved value from Type=3
Consider the use of the CASE statement
I have parameter @NextOrderNo for receive No value in case Type=4,
@NextOrderNo = (No of Type=3 )+1
and parameter @increasevalue for receive how many NextNo should be added.
consider possible change:
IF @NextOrderNo is null
begin
SELECT @OrderNo = NextNo FROM NxNo WITH(Nolock);
update NxNo set NextNo += CASE WHEN @Type IN (1,2) THEN 1 WHEN @Type = 3 THEN 2 END;
end
ELSE
SET @OrderNo = @NextOrderNo ;
INSERT INTO Orders (No, AccountNo, Product, Value, Type)
VALUES@OrderNo @AccountNo, @product, @value, @type);
Close. The SELECT and the UPDATE are best accomplished in a single statement - see JBM's post here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 8, 2012 at 5:02 am
michael vessey (6/8/2012)
a couple of things hereIF @NextOrderNo is null
begin
SET @OrderNo = (SELECT NextNo FROM NxNo (Nolock))
update NxNo set NextNo = NextNo @increasevalue
end
1) remember to use the WITH statement for your nolock - hints
2) - i really don't think you should be using nolock here - you may get conflicting orders in your table if more than one person is using the system - ie duplicate order numbers
3) - you haven't explained where you are getting the duplicates - in the orders table or in the numbers table
I am getting the duplicate while insert into Orders
June 8, 2012 at 5:05 am
wertyui (6/8/2012)
michael vessey (6/8/2012)
a couple of things hereIF @NextOrderNo is null
begin
SET @OrderNo = (SELECT NextNo FROM NxNo (Nolock))
update NxNo set NextNo = NextNo @increasevalue
end
1) remember to use the WITH statement for your nolock - hints
2) - i really don't think you should be using nolock here - you may get conflicting orders in your table if more than one person is using the system - ie duplicate order numbers
3) - you haven't explained where you are getting the duplicates - in the orders table or in the numbers table
I am getting the duplicate while insert into Orders
and is it all the time, or just ocasionally ?
MVDBA
June 8, 2012 at 10:34 am
michael vessey (6/8/2012)
wertyui (6/8/2012)
michael vessey (6/8/2012)
a couple of things hereIF @NextOrderNo is null
begin
SET @OrderNo = (SELECT NextNo FROM NxNo (Nolock))
update NxNo set NextNo = NextNo @increasevalue
end
1) remember to use the WITH statement for your nolock - hints
2) - i really don't think you should be using nolock here - you may get conflicting orders in your table if more than one person is using the system - ie duplicate order numbers
3) - you haven't explained where you are getting the duplicates - in the orders table or in the numbers table
I am getting the duplicate while insert into Orders
and is it all the time, or just ocasionally ?
It just ocasionally
June 8, 2012 at 1:38 pm
So the (Nolock) hint is probably allowing for a read where it should be blocked while the next order is being generated.
Something like this should help.
I don't see where @NextOrderNo is populated, but since this looks like a script fragment I guess earlier in the script.
BEGIN TRANSACTION
BEGIN TRY
IF @NextOrderNo IS NULL
BEGIN
SELECT
@OrderNo = NextNo
FROM
NxNo
UPDATE
NxNo
SET
NextNo = NextNo + CASE WHEN @Type IN (1,2) THEN 1
WHEN @Type = 3 THEN 2
ELSE 0
END
END
ELSE
SET @OrderNo = @NextOrderNo
INSERT INTO Orders(No,AccountNo,Product,Value,Type)
VALUES (@OrderNo,@AccountNo,@Product,@value, @type)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Personally I hate a sequences tables like this. It seems to me a better table design would have avoided a lot of this trouble. This is just my read, but your orders table and detail of the orders table appear combined. They really should be split up. The Order table would be using an Identity column to track the order numbers and the details could be managed by the application or clever use of Row_Number.
Something like this
CREATE TABLE [dbo].[Orders]
(
[No] [int] IDENTITY(1,1) NOT NULL,
[AccountNo] [varchar](6) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([No] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON
[PRIMARY]
CREATE TABLE dbo.OrdersDetail
(
[No] INT NOT NULL REFERENCES dbo.Orders([No]),
Line INT NOT NULL,
[Product] [varchar](15) NOT NULL,
[Type] [tinyint] NOT NULL,
[Value] [numeric](15,4) NOT NULL
CONSTRAINT [DF_Orders_Value] DEFAULT ((0)),
CONSTRAINT PK_OrdersDetail PRIMARY KEY CLUSTERED ([NO] ASC,Line ASC))
GO
DECLARE @OrderData TABLE (OrderNo INT)
DECLARE @LineNo INT
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.[No]
INTO @OrderData
VALUES
(102)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'AAA',
1,
1000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(111)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'BBB',
1,
5000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(103)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'AAA',
2,
5000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(101)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'AAA',
1,
5000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(111)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'BBB',
2,
5000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(102)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'BBB',
3,
5000.0000
FROM
@OrderData
SET @LineNo = @LineNo + 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'CCC',
4,
5000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(111)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'AAA',
3,
2000.0000
FROM
@OrderData
SET @LineNo = @LineNo + 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'CCC',
4,
2000.0000
FROM
@OrderData
DELETE
@OrderData
INSERT INTO Orders
(AccountNo)
OUTPUT INSERTED.No
INTO @OrderData
VALUES
(103)
SET @LineNo = 1
INSERT OrdersDetail
([No],
Line,
Product,
Type,
Value)
SELECT
OrderNo,
@LineNo,
'BBB',
2,
15000.0000
FROM
@OrderData
June 11, 2012 at 2:30 am
it's almost definatly the nolock hint
MVDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply