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