Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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

0
conrad2010
Asked:
conrad2010
  • 4
  • 4
  • 2
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now