Find Auto Increment Order Number

Jewel

I want to create an autoincrement order number. Where the numbers will start from 00001. Current year will be attached. When the new year comes, the number will start again from 00001. For example - 00001/2023 00002/2023 New Year it will be 00001/2024.

An expert helped me by writing the code below. But the problem is when I want to get it as json data via dotnet core FromSqlRaw it is not working. It would be helpful if any experienced person could help me. thanks in advance

//expert sql Code

create table NextOrderNumber ( Year int not null, Number int not null ) go declare @year int = 2023; declare @number int = 1; merge NextOrderNumber as n using (select @year as year) as y on n.Year = y.Year when matched then update set Number = Number + 1, @number = Number + 1 when not matched then insert (Year, Number) values (y.year, @number); select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4);

//my code Controller

public JsonResult GetOrderNO()

{ string query = $"declare @year int = 2025; declare @number int = 1; merge NextOrderNumber as n using (select @year as year) as y on n.Year = y.Year when matched then update set Number = Number + 1, @number = Number + 1 when not matched then insert (Year, Number) values (y.year, @number); select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4); ";

var List = _context.NextOrderNumber.FromSqlRaw(query);

return Json(List); }

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya2y ago
Hello Jewel,

Can you please try with the below code.

DECLARE @Number INT = 0;
SELECT CONCAT( FORMAT(@Number + 1, '0000'),'/',YEAR(GETUTCDATE())) AS OrderNumber

Thanks
Anandu G Nath2y ago
DECLARE @year INT = 2025;
DECLARE @newParameter INT = 123; -- Change this to your new parameter value
DECLARE @number INT;

MERGE NextOrderNumber AS n
USING (SELECT @year AS year) AS y
ON n.Year = y.Year
WHEN MATCHED THEN
UPDATE SET @number = Number + @newParameter
WHEN NOT MATCHED THEN
INSERT (Year, Number)
VALUES (y.year, @newParameter);

-- Return the generated order number
SELECT CAST(@year AS VARCHAR(4)) + RIGHT('0000' + CAST(@number AS VARCHAR(5)), 4) AS OrderNumber;
Tuhin Paul2y ago
The SQL query in the controller is not returning the expected JSON data. In your SQL query within the controller, you're using DECLARE to define local variables @year and @number. These variables will only be available within the scope of the query and won't persist between different parts of the query.

Also the MERGE statement is used for performing an INSERT, UPDATE, or DELETE operation depending on the condition. But you're trying to use it to retrieve data, which is not the intended use of the MERGE statement. Check the SQL for Generating Order Number.
```
-- SQL to generate the order number
DECLARE @year INT = 2025;
DECLARE @number INT;

MERGE NextOrderNumber AS n
USING (SELECT @year AS year) AS y
ON n.Year = y.Year
WHEN MATCHED THEN
  UPDATE SET @number = Number + 1
WHEN NOT MATCHED THEN
  INSERT (Year, Number)
  VALUES (y.year, 1);

-- Return the generated order number
SELECT CAST(@year AS VARCHAR(4)) + RIGHT('0000' + CAST(@number AS VARCHAR(5)), 4) AS OrderNumber;
```