Home > Uncategorized > Generate Random string in MS-SQL within UDF

Generate Random string in MS-SQL within UDF

Just modified some code posted on stackoverflow to generate a random string with SQL, to fit it into a UDF (User defined function) as follows;

CREATE VIEW rndView
AS
SELECT crypt_gen_random(8) rndResult

create function randomString
( ) returns varchar(8) as
BEGIN
declare @BinaryData varbinary(max), @CharacterData varchar(max)

SELECT @BinaryData = rndResult FROM rndView
set @CharacterData=cast(” as xml).value(‘xs:base64Binary(sql:variable(“@BinaryData”))’, ‘varchar(max)’)

return @CharacterData
END

The secondary view is required to get around a limitation that UDF’s are not supposed to change database state. – It does mean that it is limited to generating fixed-length random strings though (i.e. 8 chars)

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: