Can this be done without a loop

Question

Can this be done without a loop

Alan Burstein

SSC Guru

Points: 61141
More actions
October 28, 2015 at 9:02 pm

#399243

Hi folks. I needed to write a stored proc that takes generates a string of random characters @minlen to @maxlen characters long for testing some functions. What I came up with is posted below. I couldn't do it without a loop. You'll see that I used temp tables; when I attempted to use that logic in a CTE it screwed up the "length" (number of rows). Just curious if anyone has done something like this without without a loop.
Thanks.
CREATE PROC dbo.RandomStrings
(
-- include some default values
@rows int = 10000,
@minlen int = 2,
@maxlen int = 6 -- max 4225
)
/****************************************************************************************
To get 50,000 rows of random string 5 to 20 character long:
EXEC dbo.RandomStrings 50000, 5, 20
****************************************************************************************/
AS
-- prep temp tables
DECLARE
@strings TABLE (chrn int NOT NULL);
DECLARE
@stringsFinal TABLE (stringID int identity primary key, string varchar(8000) NOT NULL)
DECLARE
@chars TABLE (charid int primary key, charn int NOT NULL);
-- Insert ascii values characters that match this pattern: [!#$0-9,a-z,A-Z] into @chars
WITH
chars AS
(
SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N
FROM (VALUES
(33),(35),(36),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(65),(66),(67),(68),(69),
(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),
(88),(89),(90),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),
(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122)) N(N)
)
INSERT @chars
SELECT * FROM chars;
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
DELETE FROM @strings; --clear it out for new characters
INSERT @strings
SELECT TOP (ABS(CHECKSUM(newid())%(@maxlen-(@minlen-1)))+@minlen) -- gets me the numbers @min-2 through @maxlen
c = ABS(CHECKSUM(newid())%65)+1 -- get me 1 through 65
FROM @chars a, @chars b;
INSERT @stringsFinal (string)
SELECT SUBSTRING((SELECT REPLICATE
(
CHAR(c.charn),
CASE CHECKSUM(newid())%3 WHEN 2 THEN 2 ELSE 1 END -- 1 in 6
)
FROM @strings s
JOIN @chars c ON s.chrn = c.charid
FOR XML PATH(''), TYPE).value('.','varchar(8000)'),1,@maxlen);
SET @i = @i+1
END
-- Output results
SELECT * FROM @stringsFinal;
GO
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Alan.B (10/28/2015)
Hi folks. I needed to write a stored proc that takes generates a string of random characters @minlen to @maxlen characters long for testing some functions. What I came up with is posted below. I couldn't do it without a loop. You'll see that I used temp tables; when I attempted to use that logic in a CTE it screwed up the "length" (number of rows). Just curious if anyone has done something like this without without a loop.
Thanks.
CREATE PROC dbo.RandomStrings
(
-- include some default values
@rows int = 10000,
@minlen int = 2,
@maxlen int = 6 -- max 4225
)
/****************************************************************************************
To get 50,000 rows of random string 5 to 20 character long:
EXEC dbo.RandomStrings 50000, 5, 20
****************************************************************************************/
AS
-- prep temp tables
DECLARE
@strings TABLE (chrn int NOT NULL);
DECLARE
@stringsFinal TABLE (stringID int identity primary key, string varchar(8000) NOT NULL)
DECLARE
@chars TABLE (charid int primary key, charn int NOT NULL);
-- Insert ascii values characters that match this pattern: [!#$0-9,a-z,A-Z] into @chars
WITH
chars AS
(
SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N
FROM (VALUES
(33),(35),(36),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(65),(66),(67),(68),(69),
(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),
(88),(89),(90),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),
(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122)) N(N)
)
INSERT @chars
SELECT * FROM chars;
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
DELETE FROM @strings; --clear it out for new characters
INSERT @strings
SELECT TOP (ABS(CHECKSUM(newid())%(@maxlen-(@minlen-1)))+@minlen) -- gets me the numbers @min-2 through @maxlen
c = ABS(CHECKSUM(newid())%65)+1 -- get me 1 through 65
FROM @chars a, @chars b;
INSERT @stringsFinal (string)
SELECT SUBSTRING((SELECT REPLICATE
(
CHAR(c.charn),
CASE CHECKSUM(newid())%3 WHEN 2 THEN 2 ELSE 1 END -- 1 in 6
)
FROM @strings s
JOIN @chars c ON s.chrn = c.charid
FOR XML PATH(''), TYPE).value('.','varchar(8000)'),1,@maxlen);
SET @i = @i+1
END
-- Output results
SELECT * FROM @stringsFinal;
GO

Here's something I've borrowed and adapted from one of Jeff's posts. Doesn't scale well to high values of maxlen and you'll have to play with the character selection to get the ones you want.

declare @minlen int = 2

,@maxlen int = 6;

if object_id('dbo.JBMTest', 'U') is not null

drop table dbo.JBMTest;

select top 50000

SomeID = identity( int,1,1

),SomeLetters2 = left(ch1.c + ch2.c + ch3.c + ch4.c + ch5.c + ch6.c + ch7.c + ch8.c + ch9.c + ch10.c, lth.Ln)

,Ln = (abs(checksum(newid()) % (@maxlen - (@minlen - 1))) + @minlen)

into dbo.JBMTest

from sys.all_columns ac1

cross join sys.all_columns ac2