Need Round Robin- For loop in T-SQL

Question

Post reply

Need Round Robin- For loop in T-SQL

theunique1011

Right there with Babe

Points: 795
More actions
May 6, 2011 at 10:08 am

#250399

Can any one write Round Robin- For loop in T-SQL?
Ex: Like follows.....
(i =1; i<=10; i++)
Print i value.
Once i = 10 and the loop should start from the begin.
at any point i value must be 1 to 10 only.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

You can... Not sure why you'd want to thought. Maybe this will help:DECLARE @i INT = 0

WHILE @i <= 10

BEGIN

SET @i = (@i % 10) + 1

-- DO something

END

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

What do you need to do exactly?

DECLARE @I INT

SET @I = 1

WHILE @I < 11

BEGIN

PRINT 'Warning this is an infinite loop'

SET @I = @I + 1

IF @I = 10

BEGIN

SET @I = 1

END

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Lamprey13 (5/6/2011)
You can... Not sure why you'd want to thought. Maybe this will help:DECLARE @i INT = 0 WHILE @i <= 10 BEGIN SET @i = (@i % 10) + 1 -- DO something
END

Cool trick, never seen it before :w00t:.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

What you're talking about isn't a job for SQL. What are you actually trying to achieve?

If you insist, you could do it in a few ways. One such would be to use a CTE, e.g.

DECLARE @table AS table (i INT)

INSERT INTO @table

SELECT 1

;WITH CTE AS (

SELECT i

FROM @table

UNION ALL

SELECT i + 1

FROM CTE

WHERE i < 10)

SELECT * FROM CTE

Another would be a WHILE loop

DECLARE @i INT

SET @i = 1

WHILE (@i <= 10)

BEGIN

PRINT @i

SET @i = @i + 1

END

But, I'd just like to stress that this is not a job for SQL. Explain your overall requirement, and we'll likely be able to offer a set-based solution that will be much faster than these types of loops.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

theunique1011 Right there with Babe Points: 795 More actions · Answer 5

I have a table partitioned by 8 quarters. (partition on datetime column.). In that table i just want to maintain 2 yrs data only and also want it automatic.

That means when a new quarter starts the data in 1st quarter should be deleted and new quarters data should be stored in it.

For this i need a loop. To use 8 partitions in round robin algorithm.

Or any one know automation of partition?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Those are called jobs.

Check it out in books online.

theunique1011 Right there with Babe Points: 795 More actions · Answer 7

There is topic in books online to make partition automatic.

hope you understand my scenario.

As new quarter started 1st partition should be deleted and new quarter should be added automatically. There is no automation in either books online or msdn.

Can any one help me on this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Yet another thread on the same problem. No further replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1102345-391-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

GilaMonster (5/6/2011)
Yet another thread on the same problem. No further replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1102345-391-1.aspx

Thanks Gail, I had successfully forgotten the 3 other threads he started about this problem.

Let me be brutally blunt.

RTFM.

I can't stress this enough, step aside and hire this out.

Paul White SSC Guru Points: 150467 More actions · Answer 10

theunique1011 (5/6/2011)
TThere is no automation in either books online or msdn.

The search engine I tried came up with several pages of links, several from MSDN or Books Online. For example, there is this one:

How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005

Paul White

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

skcadavre (5/6/2011)
What you're talking about isn't a job for SQL. What are you actually trying to achieve?
If you insist, you could do it in a few ways. One such would be to use a CTE, e.g.
DECLARE @table AS table (i INT)
INSERT INTO @table
SELECT 1
;WITH CTE AS (
SELECT i
FROM @table
UNION ALL
SELECT i + 1
FROM CTE
WHERE i < 10)
SELECT * FROM CTE
Another would be a WHILE loop
DECLARE @i INT
SET @i = 1
WHILE (@i <= 10)
BEGIN
PRINT @i
SET @i = @i + 1
END
But, I'd just like to stress that this is not a job for SQL. Explain your overall requirement, and we'll likely be able to offer a set-based solution that will be much faster than these types of loops.

Yes... a CTE will do the same thing but, I'd like to caution, although a recursive CTE (rCTE) can sometimes (sometimes slower, as well) be a little bit faster than a WHILE loop, it's still RBAR and doesn't stand a chance against set-based code. Yes, there are places where an rCTE is a decent solution but, most of the time, it's no better (and is sometimes worse) than a WHILE loop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)