返回随机字串的存储过程

本来是打算写成一个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

昨天在网上看到了可以用View避开返回不确定值的rand()与newid()都不能在函数内部使用
所以改一下：

-- =============================================-- 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

方法比储存过程方便多了。