Solved

convert this usp to t-sql function

Posted on 2009-07-02
10
583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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