Simple random string script... 1

[Neil Toulouse]

Hi!

A colleague of mine passed me some code he got off the interweb for generating a simple 8 character (random?) code that he wants to implement as part of a very basic password routine.

I tidied up what he sent but I can't get it to work either. The original issue is with the NewID() function which cannot be used in a function. I have messed with RAND() and Row_Number and such like, but always end up with the string '01234567'!!

I am guessing this should be fairly easy, but a lot of the examples on the net seem to be quite complex.

Can anyone shed any light on this?

CREATE FUNCTION [dbo].[CreatePassword]()

RETURNS varchar(8)
BEGIN

DECLARE @password varchar(8)
SET @password=''
SELECT @password = @password + char(n)
FROM
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by newid()
) AS t

RETURN @password

END

The 'SELECT TOP 8....' statement works perfectly on its own, just not in a function.

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

You could create a view and a function.

The view:

Create View PasswordLetters
As
select top 8000 Char(number)  as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()

The function:

Alter FUNCTION [dbo].[CreatePassword](@Length Int)
  RETURNS varchar(8000)
As
BEGIN
  DECLARE @password varchar(8000)

  SET @password=''

  SELECT @password = @password + n
  FROM   PasswordLetters

  RETURN Left(@password, @Length)

END

This should work on any version of sql, version 2000 and greater.

Call it like this:

Select dbo.CreatePassword(12)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Hi George!

Never thought of using a view for this

Anyways, as always a cracking solution, and I thank you!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

You're welcome.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom