Simon Ordo Simon Ordo - 3 months ago 18
SQL Question

Returning while-loop values as multiple rows in SQL Server instead of multiple result sets

I have the following T-SQL, used to generate some random values:

DECLARE @cnt INT = 0;

WHILE @cnt < 100
BEGIN

select
Random_String =
substring(x,(abs(checksum(newid()))%36)+1,1)+
substring(x,(abs(checksum(newid()))%36)+1,1)+
substring(x,(abs(checksum(newid()))%36)+1,1)

from
(select x='0123456789ABCDEFGHJKLMNPQRSTUWXYZ%#-=+') a

SET @cnt = @cnt + 1;
END;


This works well enough, except every string is returned as, what looks like, an independent result set.

Is there a way to refactor that query to return every value as one row in the same result set?

Environment is MS SQL Server 2008.

Thanks!

Answer

The best way to do this kind of thing is to forget about looping in t-sql. Using a numbers or tally table is a much better way to go about this.

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a cross join E1 b), --10E+2 or 100 rows
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    )

select
    Random_String =
    substring(x,(abs(checksum(newid()))%36)+1,1)+       
    substring(x,(abs(checksum(newid()))%36)+1,1)+
    substring(x,(abs(checksum(newid()))%36)+1,1)

from
(select x='0123456789ABCDEFGHJKLMNPQRSTUWXYZ%#-=+') a
cross join cteTally t
where t.N < = 100
Comments