返回随机字串的存储过程
本来是打算写成一个function的，但想不到返回不确定值的rand()与newid()都不能在函数内部使用，只能把它做成存储过程了。
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:akuoma-- Create date: 20120822-- Description: 返回一个@len长度的随机字串-- Thinking:26个小写字符ascII('a')97~ascII('z')122，-- 26个大写字符ascII('A')65~ascii('Z')90--cast(rand()*26 as int) 返回 26 之间的数-- paramer:-- @len int 需要随机字串的长度(50个字符以内）-- @flag varchar(10) 小写随机串：lower ；大写随机串：upper ;不分 rand或空-- =============================================create Procedure dbo.GetRandStr(@len int,@flag varchar(10),@reval varchar(50) out)ASBEGIN-- Declare the return variable here--declare @reval varchar(50);declare @maxlen int;declare @randint;declare @tempfloat;set @reval='';if @len<=50 set @maxlen = @len;elseset @maxlen =50;-- Add the T-SQL statements to compute the return value herewhile(@maxlen>0)beginset @rand = cast(RAND()*26 as int)--随机小写字符if @flag='lower'set @rand = @rand+97;--随机大写字符else if @flag='upper' set @rand = @rand+65;--随机看rand()*2<=1时返回小写，反之返回大写elsebeginset @temp = RAND()*2;set @rand = case when @temp<=1 then @rand+97 else @rand+65 end;endset @reval = @reval+char(@rand);set @maxlen = @maxlen-1;end--select @reval;ENDGO结果：
exec dbo.GetRandStr 10,null,@reval;
ZgdTyiNFAI
当然，我最终的目的，只是为了做一些测试时，输入一些随机值，如下：
declare @i int; declare @name varchar(6); declare @float float; set @i=0; while(@i<1000) beginexec dbo.getrandstr 6,null,@name out;set @float= RAND();insert into Leo.dbo.person_info(name,sex,birthday,tel,inser_user,inser_date)select @name, case when RAND()*2<=1 then '男' else '女' end, case when @float<=0.1 then cast('1981-1-1' as datetime) when @float<=0.2 then cast('1982-1-1' as datetime) when @float<=0.3 then cast('1983-1-1' as datetime) when @float<=0.4 then cast('1984-1-1' as datetime) when @float<=0.5 then cast('1985-1-1' as datetime) when @float<=0.6 then cast('1986-1-1' as datetime) when @float<=0.7 then cast('1987-1-1' as datetime) when @float<=0.8 then cast('1988-1-1' as datetime) when @float<=0.9 then cast('1989-1-1' as datetime) else cast('1990-1-1' as datetime) end, null, current_user, getdate();set @i=@i+1; end-- =============================================-- Author:akuoma-- Create date: 2012-08-22-- Description:用视图避开返回不确定值的rand()与newid()都不能在函数内部使用-- =============================================Create View v_Random as select rand() as random;
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:akuoma-- Create date: 20120822-- Description: 返回一个@len长度的随机字串-- Thinking:26个小写字符ascII('a')97~ascII('z')122，-- 26个大写字符ascII('A')65~ascii('Z')90--cast(rand()*26 as int) 返回之间的数-- paramer:-- @len int 需要随机字串的长度(50个字符以内）-- @flag varchar(10) 小写随机串：lower ；大写随机串：upper ;不分rand或空-- =============================================create Function dbo.GetRandStr2(@len int, @flag varchar(10))returns varchar(50)ASBEGIN-- Declare the return variable heredeclare @reval varchar(50);declare @maxlen int;declare @randint;declare @tempfloat;set @reval='';if @len<=50 set @maxlen = @len;elseset @maxlen =50;-- Add the T-SQL statements to compute the return value herewhile(@maxlen>0)beginselect @rand=random*26 from v_Random;--随机小写字符if @flag='lower'set @rand = @rand+97;--随机大写字符else if @flag='upper' set @rand = @rand+65;--随机看rand()*2<=1时返回小写，反之返回大写elsebegin--set @temp = RAND()*2;select @rand=random*2 from v_Random;set @rand = case when @temp<=1 then @rand+97 else @rand+65 end;endset @reval = @reval+char(@rand);set @maxlen = @maxlen-1;endReturn @reval;ENDGO
方法比储存过程方便多了。