[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Function to generate random password

Posted on 2009-12-22
2
Medium Priority
?
1,000 Views
Last Modified: 2012-08-13
Hi there, I am trying to move the code from a stored proc to a function so that I may call the function in the select block of my query, I have done this over and over in oracle but inside of sql server i get the following errors....

Msg 443, Level 16, State 15, Procedure f_Password, Line 19
Invalid use of a side-effecting operator 'SET OPTION ON' within a function.
Msg 443, Level 16, State 1, Procedure f_Password, Line 28
Invalid use of a side-effecting operator 'rand' within a function.


Please see code below for complete function

USE [MyTest]
GO
/****** Object:  UserDefinedFunction [dbo].[f_Password]    Script Date: 12/22/2009 11:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_Password] ()
RETURNS varchar(25)
WITH EXECUTE AS CALLER
begin
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET NOCOUNT ON

SET @counter = 1

WHILE @counter < (8 + 1)

BEGIN

        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @RandomID = @RandomID + @CurrentCharacter

END

Return @RandomID
end;





0
Comment
Question by:ajholmes
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26106116
CREATE VIEW vRandom
as
SELECT RAND()  as Random
GO

CREATE function [dbo].[f_Password] ()
RETURNS varchar(25)
WITH EXECUTE AS CALLER
begin
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET @counter = 1

WHILE @counter < (8 + 1)

BEGIN

        SELECT @RandomNumber = Random FROM vRandom
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @RandomID = @RandomID + @CurrentCharacter

END

Return @RandomID
end;

go

select [dbo].[f_Password]()




0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106167
I use this

SELECT random from vw_random
CREATE VIEW [dbo].[vw_Random]
AS
  SELECT rand() as Random
GO

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.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

873 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