How To Implement This As A Function?

Hi all,

I've been using a piece of transact code to generate random strings of characters in a lot of scripts and I wanted to wrap it up all nice and neat into a function. I came up with the script below.

However, this fails with the following error..
quote:Invalid use of 'rand' within a function.
I've allready tried using dynamic sql and sp_executesql inside the function hoping that a new scope would let me use rand() but that isn't allowed either.

I'm Using sql server 2000. Any ideas?

Charlie.

-- snip ----

CREATE FUNCTION dbo.randstr (
@lengthINT)
RETURNS VARCHAR

AS BEGIN
DECLARE @outputVARCHAR(8000)
DECLARE @allowedVARCHAR(8000)

SET @allowed = '234679abcdefghjkmnpqrstuvwxyz234679ACDEFGHIJKLMNPQRTUVWXYZ'

SET NOCOUNT ON
DECLARE @charmapTABLE (charpos INT IDENTITY (1,1), symbol CHAR)
DECLARE @counterINT
DECLARE @characterINT
DECLARE @maplengthINT

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

-- end snip ---