Generate Random Number In Sql Server.

Create View First then Create Function.


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[RandomNewID]
AS
SELECT NEWID() AS [NewID]

GO




Create FUNCTION [dbo].[GenerateRandomPassword] ( @PasswordLength INT )
RETURNS VARCHAR(20)
AS
BEGIN


DECLARE @Password     VARCHAR(20)
DECLARE @ValidCharacters   VARCHAR(100)
DECLARE @PasswordIndex    INT
DECLARE @CharacterLocation   INT


SET @ValidCharacters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890'


SET @PasswordIndex = 1
SET @Password = ''


WHILE @PasswordIndex <= @PasswordLength
BEGIN
 SELECT @CharacterLocation = ABS(CAST(CAST([NewID] AS VARBINARY) AS INT)) %
LEN(@ValidCharacters) + 1
 FROM [dbo].[RandomNewID]


 SET @Password = @Password + SUBSTRING(@ValidCharacters, @CharacterLocation, 1)


 SET @PasswordIndex = @PasswordIndex + 1
END


RETURN @Password


END