CODEWITHSUNDEEP
sql-serverstored-procedures
Dinuka_Sahan
Dinuka_Sahan

Reputation: 5

How to increase a number with a format that i'm giving in SQL Server stored procedure

I'm using a SQL Server stored procedure to increase a customized number. I have an autonumber column and it assigns to @LASTNUMBER variable:

SET @STOCK_LEDGER_NUMBER = 'SLL' + '-' + RIGHT(('0000'+ CAST((@LASTNUMBER + 1)AS VARCHAR)),4)

With this code, I can increase my number only to a maximum of 9999.

When autonumber is set to 10000, this code returns 'SLL-0000'. But I need to show it as 'SLL-10000' and next number should be 'SLL-10001'

Please help me

Upvotes: 0

Views: 298

Answers (2)

Jason A. Long
Jason A. Long

Reputation: 4442

Give this a shot... It'll handle any positive INT value without any modification...

DECLARE 
    @last_number INT,
    @digit_count INT,
    @rep_start INT,
    @stock_ledger_number VARCHAR(14);

SELECT 
    @last_number = 10000,
    @digit_count = FLOOR(LOG10(@last_number + 1) + 1),
    @rep_start = (ABS(4 - @digit_count) + (4 - @digit_count)) / 2,
    @stock_ledger_number = STUFF('SLL-0000', 5 + @rep_start, @digit_count, CONVERT(VARCHAR(10), @last_number + 1))

SELECT last_number = @last_number, stock_ledger_number = @stock_ledger_number;

Results...

last_number stock_ledger_number
----------- -------------------
10000       SLL-10001

And a few other examples...

last_number stock_ledger_number
----------- -------------------
1           SLL-0002

last_number stock_ledger_number
----------- -------------------
55          SLL-0056

last_number stock_ledger_number
----------- -------------------
332         SLL-0333

last_number stock_ledger_number
----------- -------------------
2018        SLL-2019

last_number stock_ledger_number
----------- -------------------
12345       SLL-12346

last_number stock_ledger_number
----------- -------------------
1234567890  SLL-1234567891

last_number stock_ledger_number
----------- -------------------
2147483646  SLL-2147483647

Of course you aren't restricted to doing these one at a time with scalar variables. You can crank them out in fairly large batches...

-- same logic as the scalar variable version above but adapted to generate multiple values in a single execution.

DECLARE @last_number INT = 9900;

WITH    
    cte_Tally AS (
        SELECT TOP (15000)
            n = ROW_NUMBER() OVER (ORDER BY o1.object_id) + @last_number
        FROM
            sys.objects o1
            CROSS JOIN sys.objects o2
        )
SELECT 
    STOCK_LEDGER_NUMBER = CONVERT(VARCHAR(14), STUFF('SLL-0000', 5 + rs.rep_start, dc.digit_count, CONVERT(VARCHAR(10), t.n)))
FROM
    cte_Tally t
    CROSS APPLY ( VALUES ( CONVERT(INT, FLOOR(LOG10(t.n) + 1))) ) dc (digit_count)
    CROSS APPLY ( VALUES ((ABS(4 - dc.digit_count) + (4 - dc.digit_count)) / 2) ) rs (rep_start);
GO

Upvotes: 1

marc_s
marc_s

Reputation: 754993

If you want to support five digits numbers, just change your line of code to:

SET @STOCK_LEDGER_NUMBER = 'SLL' + '-' + RIGHT(('00000'+ CAST((@LASTNUMBER + 1) AS VARCHAR(5))), 5)

Upvotes: 4

Related Questions