Solved

convert this usp to t-sql function

Posted on 2009-07-02
10
565 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
Comment Utility

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

* 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
Comment Utility
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
Comment Utility
checking...
0
 

Author Comment

by:conrad2010
Comment Utility
error: invalid use of newid within a function...
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
Comment Utility


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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
columnName  default dbo.functionName()

dont put quotes around the function
0
 

Author Comment

by:conrad2010
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

13 Experts available now in Live!

Get 1:1 Help Now