convert this usp to t-sql function

can someone convert this stored procedure into a function?

usage: exec dbo.GenerateRandomString 1,1,0,null,5,@a OUT

the function needs to be able to be used in a default value field in a sql server table..
/***************************************************************************
* Created By: Raymond Lewallen
* Date:  05/16/2005
* Purpose: Generate a random string of given length
*
* Comments: Everything is self-explanatory.  Right now
*  max length is set to 100. So anything between 1
*  and 100 will work for a length.
*
*   If you specify a @charactersToUse,
*  the bit flags get ignored.
*
*   All spaces are stripped from the 
*  @charactersToUse.
*
*   Characters can repeat. Will be 
*  handled in a future version.
*  http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
***************************************************************************/
CREATE procedure dbo.GenerateRandomString (
 @useNumbers bit,
 @useLowerCase bit,
 @useUpperCase bit,
 @charactersToUse as varchar(100),
 @passwordLength as smallint,
 @password varchar(100) OUT
)
As
Begin
 
if @passwordLength <= 0
 raiserror('Cannot generate a random string of zero length.',16,1)
 
declare @characters varchar(100)
declare @count int
 
set @characters = ''
 
if @useNumbers = 1
begin
 -- load up numbers 0 - 9
 set @count = 48
 while @count <=57
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useLowerCase = 1
begin
 -- load up uppercase letters A - Z
 set @count = 65
 while @count <=90
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useUpperCase = 1
begin
 -- load up lowercase letters a - z
 set @count = 97
 while @count <=122
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
set @count = 0
set @password = ''
 
-- If you specify a character set to use, the bit flags get ignored.
if Len(@charactersToUse) > 0
begin
 while charindex(@charactersToUse,' ') > 0
 begin
  set @charactersToUse = replace(@charactersToUse,' ','')
 end
 
 if Len(@charactersToUse) = 0
  raiserror('Cannot use an empty character set.',16,1)
 
 while @count <= @passwordLength
 begin
     set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
     set @count = @count + 1
 end
end
else
begin
 while @count <= @passwordLength
 begin
     set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
     set @count = @count + 1
 end
end
set @password = 'bR' + @password
end
 
GO

Open in new window

conrad2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:

/***************************************************************************
* Created By: Raymond Lewallen
* Date:  05/16/2005
* Purpose: Generate a random string of given length
*
* Comments: Everything is self-explanatory.  Right now
*  max length is set to 100. So anything between 1
*  and 100 will work for a length.
*
*   If you specify a @charactersToUse,
*  the bit flags get ignored.
*
*   All spaces are stripped from the 
*  @charactersToUse.
*
*   Characters can repeat. Will be 
*  handled in a future version.
*  http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/17/63281.aspx
***************************************************************************/
CREATE FUNCTION dbo.GenerateRandomString (
 @useNumbers bit,
 @useLowerCase bit,
 @useUpperCase bit,
 @charactersToUse as varchar(100),
 @passwordLength as smallint
)
RETURNS varchar(100)
As
Begin
DECLARE @password varchar(100);
 
if @passwordLength <= 0
 raiserror('Cannot generate a random string of zero length.',16,1)
 
declare @characters varchar(100)
declare @count int
 
set @characters = ''
 
if @useNumbers = 1
begin
 -- load up numbers 0 - 9
 set @count = 48
 while @count <=57
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useLowerCase = 1
begin
 -- load up uppercase letters A - Z
 set @count = 65
 while @count <=90
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useUpperCase = 1
begin
 -- load up lowercase letters a - z
 set @count = 97
 while @count <=122
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
set @count = 0
set @password = ''
 
-- If you specify a character set to use, the bit flags get ignored.
if Len(@charactersToUse) > 0
begin
 while charindex(@charactersToUse,' ') > 0
 begin
  set @charactersToUse = replace(@charactersToUse,' ','')
 end
 
 if Len(@charactersToUse) = 0
  raiserror('Cannot use an empty character set.',16,1)
 
 while @count <= @passwordLength
 begin
     set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
     set @count = @count + 1
 end
end
else
begin
 while @count <= @passwordLength
 begin
     set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
     set @count = @count + 1
 end
end
set @password = 'bR' + @password
 
RETURN @password;
end
 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
As you see, just moved the out parameter to be the RETURN type along with supporting code to ensure it is returned at end of func.
0
conrad2010Author Commented:
checking...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

conrad2010Author Commented:
error: invalid use of newid within a function...
0
Aneesh RetnakaranDatabase AdministratorCommented:


drop view vw_NewID
GO
create view vw_NewID
as
select NEWID() as NewID
GO

create view vw_RandID
as
select RAND() as RandID
GO

CREATE FUNCTION dbo.GenerateRandomString (
 @useNumbers bit,
 @useLowerCase bit,
 @useUpperCase bit,
 @charactersToUse as varchar(100),
 @passwordLength as smallint
)
RETURNS varchar(100)
As
Begin
DECLARE @password varchar(100);
 
--if @passwordLength <= 0
-- raiserror('Cannot generate a random string of zero length.',16,1)
 
declare @characters varchar(100)
declare @count int
 
set @characters = ''
 
if @useNumbers = 1
begin
 -- load up numbers 0 - 9
 set @count = 48
 while @count <=57
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useLowerCase = 1
begin
 -- load up uppercase letters A - Z
 set @count = 65
 while @count <=90
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
if @useUpperCase = 1
begin
 -- load up lowercase letters a - z
 set @count = 97
 while @count <=122
 begin
     set @characters = @characters + Cast(CHAR(@count) as char(1))
     set @count = @count + 1
 end
end
 
set @count = 0
set @password = ''
 
-- If you specify a character set to use, the bit flags get ignored.
if Len(@charactersToUse) > 0
begin
 while charindex(@charactersToUse,' ') > 0
 begin
  set @charactersToUse = replace(@charactersToUse,' ','')
 end
 
 --if Len(@charactersToUse) = 0
 -- raiserror('Cannot use an empty character set.',16,1)
 
 declare @newID uniqueidentifier
 declare @RandID numeric(10,8)
 
 while @count <= @passwordLength
 begin
       SELECT @newID = [NewID] from vw_NewID
       SELECT @RandID = RandID from vw_RandID       
     set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(@newID))*@RandID*(@count) as int)%LEN(@charactersToUse)+1,1)
     set @count = @count + 1
 end
end
else
begin
 while @count <= @passwordLength
 begin
       SELECT @newID = [NewID] from vw_NewID
       SELECT @RandID = RandID from vw_RandID       
 
     set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(@newID))*@RandID*(@count) as int)%LEN(@characters)+1,1)
     set @count = @count + 1
 end
end
set @password = 'bR' + @password
 
RETURN @password;
end
 
GO

--limitations,
you cannot use the RAISERROR within the function
0
Kevin CrossChief Technology OfficerCommented:
Sorry I should have looked better at the inner workings of the code.

RAND() and NEWID() won't be allowed:
http://www.dbtechie.com/sql-server/user-defined-functions-udf/random-thoughts-on-sql-server/
The linked article shows how to get around using a view.

The RAISEERROR lines will need to be changed as well.  Maybe return NULL at those points instead.
0
conrad2010Author Commented:
this seems to work, but I'm not able to set this function as a default value on a string field in SQL 2000...

SQL Server changes the default value to  N'fnGenerateRandomString()'

is it possible to use the output of a function automatically? something like getdate()...
0
Aneesh RetnakaranDatabase AdministratorCommented:
columnName  default dbo.functionName()

dont put quotes around the function
0
conrad2010Author Commented:
I'm using the SQL Server Enterprise Manager and each time I put the function name in the Default value, it's automatically changed to N'dbo.fnGenerateRandomString()' when I exit the field...
0
Kevin CrossChief Technology OfficerCommented:
It won't work through Enterprise Manager or SQL Management Studio at least as far as I know.  For existing column, you can do this though.
alter table your_table_name
add constraint df_password default (dbo.GenerateRandomString(1,1,0,null,5)) for your_column_name

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.