终于搞出来了,还是自己想想好 --产生随机卡号,带一个输出参数,输出随机卡号,输出格式为’xxxx xxxx xxxx xxxx’ if exists(select * from sys.objects where name='createNO') begin drop proc createNO end go create proc createNO @scid char(50) output as begin select @scid=((Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))) declare @i int set @i=1 --判断是否存在相同的卡号 while(1>0) begin if(@scid=(select cardID from cardInfo where customerId=@i)) begin select @scid=((Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))) set @i=@i+1 end else begin break end end end go declare @scid char(50) exec createNO @scid output select @scid
BEGIN
select @a,(Substring(convert(varchar(50),rand()*10000000000),1,4))
SET @a=@a+1
END /*
----------- --------
1 9.85
----------- --------
2 5.49
----------- --------
3 7.38
----------- --------
4 7.62
----------- --------
5 9.49
----------- --------
6 3.22
----------- --------
7 5.05
----------- --------
8 7.05
----------- --------
9 6.13
----------- --------
10 1.49*/
--产生随机卡号,带一个输出参数,输出随机卡号,输出格式为’xxxx xxxx xxxx xxxx’
if exists(select * from sys.objects where name='createNO')
begin
drop proc createNO
end
go
create proc createNO
@scid char(50) output
as
begin
select @scid=((Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4)))
declare @i int
set @i=1
--判断是否存在相同的卡号
while(1>0)
begin
if(@scid=(select cardID from cardInfo where customerId=@i))
begin
select @scid=((Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4))+' '+(Substring(convert(varchar(50),rand()),3,4)))
set @i=@i+1
end
else
begin
break
end
end
end
go
declare @scid char(50)
exec createNO @scid output
select @scid
float转换成varchar时会变成科学计数法,如:5.18e+009
substring(1,4)从第一个字符起取4个字符,即“5.18”。