我想用一个存储过程做以下的操作: create proc p_rand @rand int output as begin---这里开始写内容 end 1、在sql中产生一个五位数的随机数 set @rand=convert(int, 100000*rand())2、产生的随机数放入表中,这个随机数不能重复,如果重复了就重新生成 表:randTable,字段 randNumber 3、符合条件的随机数要返回请各位高手帮忙解决一下。有点急(*^__^*) ……。。谢谢
create proc p_rand @rand int output as begin set @rand=convert(int, 100000*rand()) if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand) begin insert into randTable values(@rand) end else set @rand = 0 end
CREATE PROC p_rand @rand INT OUTPUT AS BEGIN WHILE 1 = 1 BEGIN SET @rand = CONVERT(INT, 100000 * RAND()) IF @rand BETWEEN 10000 AND 99999 AND NOT EXISTS ( SELECT 1 FROM randTable WHERE randNumber = @rand ) BEGIN INSERT INTO randTable VALUES ( @rand ) BREAK ; END ELSE CONTINUE ;
END END --把楼上的改了下,一定会成功.
第一次没有成功,是可以用ifelse 来判断,,但是第二次还是没有成功呢。 那不是没有办法在返回了
create proc p_rand @rand int output as begin --这里开始写内容 declare @a as int=1 WHILE @a=1 BEGIN SET @rand=convert(int, 100000*rand()) IF NOT EXISTS(SELECT 1 FROM TB WHERE 列=@rand) BEGIN INSERT INTO TB(列) SELECT @rand SET @a=2 END END END
更改为如下: create proc p_rand @rand int output as begin while @rand not between 10000 and 99999 begin set @rand=convert(int, 100000*rand()) if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand) insert into randTable values(@rand) end end
create table randTable (randNumber int) gocreate proc p_rand @rand int output as begin set @rand = 0 --这里漏写一个初始值,不好意思. while @rand not between 10000 and 99999 begin set @rand=convert(int, 100000*rand()) if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand) insert into randTable values(@rand) end end godeclare @rand as int exec p_rand @rand OUTPUT--查询返回值 select @rand /*
create proc p_rand @rand int output as begin --这里开始写内容 declare @a as int=1 WHILE @a=1 BEGIN SET @rand=convert(int, 100000*rand()) IF NOT EXISTS(SELECT 1 FROM TB WHERE 列=@rand) BEGIN INSERT INTO TB(列) SELECT @rand select @rand --返回随即数.过程没有return语句.直接select就返回了 SET @a=2 END END END
@rand int output
as
begin
set @rand=convert(int, 100000*rand())
if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand)
begin
insert into randTable values(@rand)
end
else
set @rand = 0
end
AS
BEGIN
WHILE 1 = 1
BEGIN
SET @rand = CONVERT(INT, 100000 * RAND())
IF @rand BETWEEN 10000 AND 99999
AND NOT EXISTS ( SELECT 1
FROM randTable
WHERE randNumber = @rand )
BEGIN
INSERT INTO randTable
VALUES ( @rand )
BREAK ;
END
ELSE
CONTINUE ;
END
END
--把楼上的改了下,一定会成功.
那不是没有办法在返回了
create proc p_rand
@rand int output
as
begin
--这里开始写内容
declare @a as int=1
WHILE @a=1
BEGIN
SET @rand=convert(int, 100000*rand())
IF NOT EXISTS(SELECT 1 FROM TB WHERE 列=@rand)
BEGIN
INSERT INTO TB(列) SELECT @rand
SET @a=2
END
END
END
create proc p_rand
@rand int output
as
begin
while @rand not between 10000 and 99999
begin
set @rand=convert(int, 100000*rand())
if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand)
insert into randTable values(@rand)
end
end
gocreate proc p_rand
@rand int output
as
begin
set @rand = 0 --这里漏写一个初始值,不好意思.
while @rand not between 10000 and 99999
begin
set @rand=convert(int, 100000*rand())
if @rand between 10000 and 99999 and not exists(select 1 from randTable where randNumber = @rand)
insert into randTable values(@rand)
end
end
godeclare @rand as int
exec p_rand @rand OUTPUT--查询返回值
select @rand
/*
-----------
82527(所影响的行数为 1 行)
*/--查询表
select * from randtable
/*
randNumber
-----------
82527(所影响的行数为 1 行)
*/drop proc p_rand
drop table randtable--select convert(int, 100000*rand())
create proc p_rand
@rand int output
as
begin
--这里开始写内容
declare @a as int=1
WHILE @a=1
BEGIN
SET @rand=convert(int, 100000*rand())
IF NOT EXISTS(SELECT 1 FROM TB WHERE 列=@rand)
BEGIN
INSERT INTO TB(列) SELECT @rand
select @rand --返回随即数.过程没有return语句.直接select就返回了
SET @a=2
END
END
END