SQL LOOP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL LOOP

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-10-07 : 13:17:31

Below I have code that goes through a table and where it finds that there is a number with 6 digits in length its adds a 0,1,2,3.... to the end
EXAMPLE

217728

Since this is 6 digits long it’s going to take that and loop this

2177281
2177282
2177283
2177284
2177285
2177286
2177287
2177288
2177289
2177280

Which is what I want to do but the way I have it setup it creates these in 10 different result sets.

Is there a way so that it loops through and creates these in one result set??

declare @counter varchar(2)

set @counter = 0
while @counter < 10

begin

Select CONVERT(varchar(7),Victory_GlobalX_npanxx+''+@counter),Victory_GlobalX_inter,Victory_GlobalX_intra
FROM Victory_GlobalX
Where LEN(Victory_GlobalX_npanxx) = 6

set @counter = @counter + 1
end

Thanks for your help,

Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 13:24:34

If you can add a tally table to your database (with your numbers to add), then you can CROSS JOIN to it to get your result set. No looping needed with this solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-07 : 13:39:27

Such as:

;WITH Tally(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM Tally WHERE n<9)
Select Victory_GlobalX_npanxx+CAST(n as varchar), Victory_GlobalX_inter, Victory_GlobalX_intra
FROM Victory_GlobalX
CROSS JOIN Tally
Where LEN(Victory_GlobalX_npanxx) = 6

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-10-07 : 13:44:41

Thank you very much....that works perfectly

quote:
Originally posted by tkizer

If you can add a tally table to your database (with your numbers to add), then you can CROSS JOIN to it to get your result set. No looping needed with this solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 14:05:06

Thanks Rob. Something for me to learn.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-07 : 14:15:01

Awwww, CTEs are da bomb diggety. I'm actually having trouble NOT writing them these days.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 14:23:32

I don't do much SQL development work, so I'm a bit behind on my T-SQL skills.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-08 : 04:40:15

quote:

Originally posted by robvolk

Such as:

;WITH Tally(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM Tally WHERE n<9)
Select Victory_GlobalX_npanxx+CAST(n as varchar), Victory_GlobalX_inter, Victory_GlobalX_intra
FROM Victory_GlobalX
CROSS JOIN Tally
Where LEN(Victory_GlobalX_npanxx) = 6

Man that had me scratching my head for a while. I was wondering why you needed to use a recursive call to populate the CTE when a straight select from tally would do the job. It took me a while to work out that this


;WITH Tally AS (SELECT [n]-1 AS [n] FROM tally WHERE n <= 10)
SELECT * FROM Tally

Which results in:

Msg 252, Level 16, State 1, Line 2
Recursive common table expression 'Tally' does not contain a top-level UNION ALL operator.

Needed to be this


;WITH Tally AS (SELECT [n]-1 AS [n] FROM dbo.tally WHERE n <= 10)
SELECT * FROM Tally

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources