本来是打算写成一个function的，但想不到返回不确定值的rand()与newid()都不能在函数内部使用，只能把它做成存储过程了。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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)
AS
BEGIN
	-- Declare the return variable here	
	--declare @reval varchar(50);
	declare @maxlen int;
	declare @rand	int;
	declare @temp	float;
	set @reval='';
	if @len<=50 
		set @maxlen = @len;
	else
		set @maxlen =50;

	-- Add the T-SQL statements to compute the return value here
	while(@maxlen>0)
	begin
		set @rand = cast(RAND()*26 as int)		
		--随机小写字符
		if @flag='lower'
			set @rand = @rand+97;
		--随机大写字符
		else if @flag='upper' 
			set @rand = @rand+65;
		--随机看rand()*2<=1时返回小写，反之返回大写
		else
		begin
			set @temp = RAND()*2;
			set @rand = case when @temp<=1 then  @rand+97 else @rand+65 end;
		end
		set @reval = @reval+char(@rand);
		set @maxlen = @maxlen-1;
	end
	--select @reval;
END
GO

结果：

exec dbo.GetRandStr 10,null,@reval;

ZgdTyiNFAI

当然，我最终的目的，只是为了做一些测试时，输入一些随机值，如下：

declare @i int;
  declare @name varchar(6);
  declare @float float;
  set @i=0;
  while(@i<1000)
  begin
	exec 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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)
AS
BEGIN
	-- Declare the return variable here	
	declare @reval varchar(50);
	declare @maxlen int;
	declare @rand	int;
	declare @temp	float;
	set @reval='';
	if @len<=50 
		set @maxlen = @len;
	else
		set @maxlen =50;

	-- Add the T-SQL statements to compute the return value here
	while(@maxlen>0)
	begin
		select @rand=random*26 from v_Random;	
		--随机小写字符
		if @flag='lower'
			set @rand = @rand+97;
		--随机大写字符
		else if @fl