A Variable Length Random String

I was looking through some of the active threads on SQLServerCentral.com tonight, and I ran across one with some code that looked familiar.  While reading this thread, I noticed that the function returned a fixed length random character string.  This told me that if you needed a random character string of a different length, you'd need another function.

How can you get around this?  You can't use the function NEWID() in a function, SQL Server 2005 doesn't like it.  There is a way to do this.  Create a view that returns the result of the function NEWID(), and use that in your function to return a random string of different lengths.

 

Here is the code I put together:

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