quote:

---

Invalid use of 'rand' within a function.

---

CREATE FUNCTION dbo.randstr (
	@length		INT)
RETURNS VARCHAR

AS BEGIN
	DECLARE @output		VARCHAR(8000)
	DECLARE @allowed	VARCHAR(8000)

	SET @allowed = '234679abcdefghjkmnpqrstuvwxyz234679ACDEFGHIJKLMNPQRTUVWXYZ'
	
	SET NOCOUNT ON
	DECLARE @charmap	TABLE (charpos INT IDENTITY (1,1),  symbol CHAR)
	DECLARE @counter	INT
	DECLARE @character	INT
	DECLARE @maplength	INT

	SET @maplength = LEN(@allowed)
	SET @character = 0
	SET @output = ''

	--  Insert the allowed characters into the character map table
	SET @counter = 1
	WHILE (@counter < @maplength + 1) BEGIN
		INSERT INTO @charmap SELECT SUBSTRING(@allowed,@counter,1)
		SET @counter = @counter + 1
	END

	--  Start the loop to generate @output
	SET @counter = 1
	WHILE (@counter < @length + 1) BEGIN
		
		--  RAND() should output a floating point number between 0 and 1 however...
		--  Sometimes the rand() function spits out a number greater than 1 
		--  or the multiplication results in 0
		WHILE (@character < 1) OR (@character > @maplength) BEGIN
			SET @character = CAST(@maplength * RAND()+1 AS INT)
		END
		
		--  Add the random character to @output
		SET @output = @output + (SELECT symbol FROM @charmap WHERE charpos = @character)	

		--  Increment and Reset the @character variable
		SET @counter = @counter + 1
		SET @character = 0
	END
	

	RETURN @output
END