Simon Ordo - 1 year ago 155

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!

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**