vendredi 16 janvier 2015

SQL Server Insert Random

i have this table:



Create Table Person
(
Consecutive Integer Identity(1,1),
Identification Varchar(15) Primary Key,
)


The Identification column can contain letters, numbers, and is optional, i.e., the customer can enter it or not, if not, creates a number automatic.. how can i do to insert a random number that does not exist before?, preferably a lower number.


A example could be:



Select Random From Person Where Random Not Exists In Identification


This is my code:



Select Min(Convert(Integer,Identification)) - 1
From Person
Where IsNumeric(Identification) = 1


Or



Select Max(Convert(Integer,Identification)) + 1
From Person
Where IsNumeric(Identification) = 1


Works well, but if the customer enter a number high, for example 1000, or higher, then the number will begin from there could have an overflow error


But if there is not a number below Identification and greater than 0 then well be -1, -2, -3.. etc.


Thanks in advance..





Aucun commentaire:

Enregistrer un commentaire