SQL Storage Procedure Example 1: Generate string randomly

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <4/29/2007>
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[SP_GenerateString](
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@length as smallint,
@randomString varchar(100) OUT
)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF (@length <= 0)
raiserror('Cannot generate a random string with zero length', 16, 1)
DECLARE @characters varchar(100)
DECLARE @count int
SET @count=0
SET @characters = ''

IF(@useNumbers = 1)
BEGIN
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
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
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 @randomString = ''
WHILE(@count < @length)
BEGIN
SET @randomString = @randomString + SUBSTRING(@characters, CAST(1000000*RAND(@count)
as int)%LEN(@characters) + 1, 1)
SET @count = @count + 1
END
END