I want to generate new id based on previous id in sql stored procedure

Question

I have one table which will be stored qno as 'res/12-19/001'.

I want to Increment it by 1 but when I increment it by 1 the new qno will be res/12-19/2.

I want it to be res/12-19/002.

I am using following statements in sql stored procedure:-

Select top (1) @OriginalQNo=QNO from Quo order by QNO desc

SET @NewQNo=RIGHT(@OriginalQNo, 3) + 1

Possible duplicate of [SQL Server - Create a custom auto-increment field](https://stackoverflow.com/questions/27845180/sql-server-create-a-custom-auto-increment-field) — Ahmed Yousif, May 20 '19 at 07:38
Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — Matt, May 20 '19 at 07:44
Why convert `@OriginalQNo` to a `varchar` and then to an `int`? What you're describing seems to be a `SEQUENCE`, as your above SQL will give you numbers 0-999. — Thom A, May 20 '19 at 07:44
*"I have one table which will be stored qno as 'res/12-19/001'."* That's the source of your problems. Seems like you are cramping up multiple data points into a single column, which is never a good idea to begin with. You should use separate columns to store separate data points. If you need this format for display, it's as easy as `select col1 + '/' + col2 +'/'+ col3 from...` — Zohar Peled, May 20 '19 at 09:09

score 1 · Accepted Answer · answered May 20 '19 at 07:47

To solve your problem as described in your question, consider the following:

DECLARE @NewQNo         VARCHAR(20)
DECLARE @OriginalQNo    VARCHAR(20)
SET @OriginalQNo = 'res/12-19/001'

SET @NewQNo = RIGHT('00' + CAST(RIGHT(@OriginalQNo, 3) + 1 AS VARCHAR), 3)
SET @NewQNo = LEFT(@OriginalQNo, LEN(@OriginalQNo) - 3) + @NewQNo

PRINT @NewQNo

In order to format the number as a 3-digit one with trailing zeroes, you simply prepend two zeros as a string to the start of the number and then extract the right-most three digits, which will produce in this case 002.

In order to also include the rest of the question syntax, the second SET line prepends the starting characters of the question (in this case, res/12-19/ to the completed 3-digit string.

Output:

res/12-19/002

score 0 · Answer 2 · answered May 20 '19 at 12:57

Assume that you are using SQL Server 2017

Try this:

DECLARE @OriginalQNo VARCHAR(MAX) = 'res/12-19/001';

SELECT STRING_AGG(K.NewValue, '/')
FROM
(
    SELECT K.value,
           K.RN,
           CASE RN
               WHEN 3 THEN
                   RIGHT(REPLICATE('0', 3) + CAST(K.value + 1 AS VARCHAR(3)), 3)
               ELSE
                   K.value
           END AS NewValue
    FROM
    (
        SELECT value,
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
        FROM STRING_SPLIT(@OriginalQNo, '/')
    ) AS K
) AS K;

Read more about STRING_SPLIT, REPLICATE, STRING_AGG

I want to generate new id based on previous id in sql stored procedure

2 Answers2