CODEWITHSUNDEEP
sql-server
user1662936
user1662936

Reputation: 41

SQL Server : generate continuous number

There is my table :

ID  code(varchar)
1   006
2   008
3   005
4   010
5   002

I have created a stored procedure:

CREATE PROC pp
    @code VARCHAR(10),
    @new_code VARCHAR(10) OUTPUT
AS
BEGIN 
    SELECT TOP 1 @new_code = RIGHT(1000+code+1,3) 
    FROM tb AS a 
    WHERE [Code] >= @code
    AND NOT EXISTS (SELECT 1 FROM tb WHERE [Code]=RIGHT(1000+a.[Code]+1,3)) 
    ORDER BY RIGHT(1000+code+1,3)
END

When I call this stored procedure below the result is 011, it is correct:

DECLARE @code VARCHAR(3)

EXEC pp '009', @code OUTPUT

SELECT @code

When I call the stored procedure below, the result is 011 remain, so the result is wrong. It should be '004'.

DECLARE @code VARCHAR(3)

EXEC pp '003', @code OUTPUT

SELECT @code

How to correct this? TKS!

Upvotes: 0

Views: 552

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

By looking at your sproc,it looks like you just need to increment the @code parameter value by 1. For that why are you doing all this calculation in sproc.Like you are selecting from tb and checking it for > somevalue and all.

if you want to only increment the value there are lot better easier ways.

Upvotes: 0

Seth Flowers
Seth Flowers

Reputation: 9180

It probably has something to do with the fact that you are selecting top 1, and ordering by a field that is not a number, but is a varchar, when you are expecting the order by to behave like a number. Have you tried casting/converting the [Code] field to an int, and then ordering by that value?

Upvotes: 1

Related Questions