--产生随机卡号的存储过程(被其它存储过程调用)
if exists(select * from sysobjects where name='proc_randCardID')
drop procedure proc_randCardID
go
create procedure proc_randCardID
@randCardID varchar(20) output
as
declare @r numeric(15,8)
declare @tempStr  char(10)
select @r=rand((datePart(mm, getDate()) * 100000 )+ (datePart(ss, getDate()) * 1000 ) + datePart(ms, getDate()))
set @tempStr=convert(char(10),@r) --产生0-1的随机数----0.xxxxxxxx的数字,我们需要小数点后的八位数字
     set @randCardID='1010 3576 '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4) --拼接
go
--测试产生随机卡号
declare @mycardID varchar(20)
exec proc_randCardID @mycardID output
print '产生的随机卡号为：'+@mycardID
go
--开户的存储过程
if exists(select * from sysobjects where name='proc_openAccount')
drop procedure proc_openAccount
go
create procedure proc_openAccount
@customerName varchar(20),
@PID varchar(20),
@telephone varchar(15),
@openMoney money,
@savingType varchar(10),
@address varchar(50)='BeiJing'
as
declare @mycardID varchar(20)
exec proc_randCardID @mycardID output --执行此存储过程，得到产生的随机数
 while exists(select * from cardInfo where cardID=@mycardID)
begin
exec proc_randCardID @mycardID output
print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID
print '开户日期'+convert(char(10),getdate(),111)+'  开户金额:'+convert(varchar(20),@openMoney)
end
if not exists(select * from userInfo where PID=@PID)
begin
insert into userInfo(customerName,PID,telephone,address) values (@customerName,@PID,@telephone,@address)
declare @cur_customerID int
select @cur_customerID=customerID from userInfo where PID=@PID
insert into cardInfo(cardID,savingType,openMoney,balance,customerID) values (@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
end
go