Simon Ordo - 10 months ago 55

SQL Question

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
```

Source (Stackoverflow)