sql server - want to generate coupon code 5 digit number -

this question has answer here:

• tsql generate 5 character length string, digits [0-9] doesn't exist in database 6 answers

i want create coupon code generator using sql database don't know how generate 1000 of random number without repeating them. can me it's important.

records in relational database tables unordered nature. therefor, can create table has values between @first , @last (0 , 9999 in case), , use random order when selecting table. can use simple int in database table , format when select data table.

since main database sql server, , have no experience sqlite, use sql server syntax in code example, , leave find sqllite equivalent.

first, create table:

create table tbl  (     intvalue int primary key,     isused bit not null default 0 ) 

then, populate numbers between 0 , 9999:

;with cte (     select 0 intvalue     union     select  intvalue + 1     cte     intvalue + 1 < 10000 )  insert tbl (intvalue) select intvalue cte option(maxrecursion 0) 

then, want select multiple values each time, write stored procedure this:

create procedure stp_getcouponcodes (     @number int = 5 -- or whatever number default ) begin   declare @usedvalues table   (       intvalue int   )    begin try    begin transaction    insert @usedvalues    select top(@number) intvalue   tbl   isused = 0   order newid()    update tbl   set isused = 1   tbl    inner join    @usedvalues uv on(tbl.intvalue = uv.intvalue)    select right('00000' + cast(intvalue varchar), 5)   @usedvalues     commit transaction   end try    begin catch      if @@trancount > 0         rollback transaction   end catch end 

then, when ever want generate coupons, execute stored procedure number of coupons want:

exec stp_getcouponcodes 10; 

see working fiddle example here.