Solved

convert this usp to t-sql function

Posted on 2009-07-02
10
570 Views
Last Modified: 2012-06-21
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
Comment
Question by:conrad2010
  • 4
  • 4
  • 2
10 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24767709

/***************************************************************************

* 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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24767719
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
 

Author Comment

by:conrad2010
ID: 24767749
checking...
0
 

Author Comment

by:conrad2010
ID: 24767762
error: invalid use of newid within a function...
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 24767828


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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24767846
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
 

Author Comment

by:conrad2010
ID: 24767959
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24768067
columnName  default dbo.functionName()

dont put quotes around the function
0
 

Author Comment

by:conrad2010
ID: 24768094
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24768254
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now