First, I was slightly confused, as the code Lowell posted uses a CTE and this is in a SQL Server 7, 2000 forum.

But, since it worked, I thought I'd throw something else out for you all. This code looks familiar, but I made a slight change.

First, I created a view that returns a NEWID as NewIdValue. I then use this view in my function where Lowell had the NEWID() function in his view. The difference, a single function that takes as a paramter the length of the random string to be returned.

create view dbo.MyNewID as

select newid() as NewIDValue;

go

create function dbo.ufn_RandomString(

@pStringLength int = 20

) returns varchar(max)

as begin

declare @RandomString varchar(max);

with

a1 as (select 1 as N union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1 union all

select 1),

a2 as (select

1 as N

from

a1 as a

cross join a1 as b),

a3 as (select

1 as N

from

a2 as a

cross join a2 as b),

a4 as (select

1 as N

from

a3 as a

cross join a2 as b),

Tally as (select

row_number() over (order by N) as N

from

a4)

, cteRandomString (

RandomString

) as (

select top (@pStringLength)

substring(x,(abs(checksum((select NewIDValue from MyNewID)))%36)+1,1)

from

Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

)

select @RandomString =

replace((select

',' + RandomString

from

cteRandomString

for xml path ('')),',','');

return (@RandomString);

end

GO