SQL to define a 7 digit serial number, and the single digits for '4', single digits to 3, the next jump straight to 5, other digits without limiting,

CodePudding user response:

CASE RIGHT ((SELECT MAX (serial number) FROM table), 1) the WHEN 3 THEN (SELECT MAX (serial number) FROM table) + 2 ELSE (SELECT MAX (serial number) FROM table) + 1 END

CodePudding user response:

SQL Server version, please.

CodePudding user response:

If is SQL2012 and above version, you can use the sequence implementation,

 
- create sequence 
The create sequence dbo. Seq0507 
As bigint 
Start with 1 
Increment by 1; 

- create take number stored procedure 
The create proc dbo. GeneratedNewID (@ r char (7) the output) 
As 
The begin 
Set nocount on 
Declare @ n bigint 

The select value for dbo. @ n=next seq0507 

If (right (convert (varchar, @ n), 1)='4') 
The begin 
The select value for dbo. @ n=next seq0507 
End 

Select @ r=replicate (' 0 ', 7 - len (convert (varchar, @ n))) + the convert varchar, @ (n) 

Return @ r 
End 


No. - take 
Declare @ LSH char (7) 
The exec dbo. GeneratedNewID @ LSH output 
Select 'serial number=@ LSH 

CodePudding user response:

 
Declare @ skip_number int 
Declare @ int I=0 

The set @ skip_number=4 

Declare @ test as table 
(
Seqno int 
) 
Insert into @ test (seqno) 
Values (2981091) 

While (@ i<=20) 
The begin 
Insert into @ test (seqno) 
Select a case when (Max (seqno) + 1) % 10=@ skip_number then Max (seqno) + 2 else Max (seqno) + 1 end 
The from @ test 

The set @ I=@ I + 1 
End 

Select * from @ test