Why is it so difficult to do a loop in T-SQL

Question

OK, I know it can be done, I do it quite often, but why so difficult to do a loop in T-SQL? I can think of a ton of reasons I'd want to parse thru a query result set and do something that simply can't be done without a loop, yet the code to setup and execute my loop is > 20 lines.

I'm sure others have a similar opinions so why are we still without a simple way to perform a loop?

An aside: we finally got an UPSERT (aka MERGE) in SQL2008 so maybe all hope isn't lost.

Answer 1

SQL is a set-based, or declarative language, not a procedural, or imperative language. T-SQL tries to straddle the two, but it's still built on a fundamentally set-based paradigm.

Answer 2

Because SQL is a Set based language. The power of sql is in find a smaller group within a larger group of data based on specific characteristics. To handle this task, looping is largely unnecessary. Obviously it's been added for convenience of handling some situations, but the intended use of the language make this feature irrelevant.

Answer 3

T-SQL is not designed to be an imperative language. Its designed to be declarative. Its declarative nature allows the optomizer to slice up the various tasks and run them in parrallel and in other ways do things in an order that is most efficient.

Answer 4

I'm not an expert in DB's but I believe the atomic nature of database transactions would make loops difficult to achieve because the transaction be complete or it should not occur at all. Maintaining state can be pesky!

Wikipedia Article on Atomicity

Answer 5

SQL is a SET based system, not a procedural (loop) one. Generally its regarded as bad practice to use loops in SQL because they perform poorly compared to thier set based equivalents.

WHILE is the most common looping structure, CURSORS can also be used, but have their own problems (forgetting to deallocate/ close)

...an example of WHILE (you may not need it but others may)

DECLARE @iterator INT
SET @iterator = 0

WHILE @iterator < 20
BEGIN
  SELECT * FROM table WHERE rowKey = @iterator
/*do stuff*/
  @iterator = @iterator + 1
END

The real question is "What is it that you are trying to do that simply cannot be done in a set based way?"

Answer 6

I can think of a ton of reasons I'd want to parse thru a query result set and do something that simply can't be done without a loop

And for the vast majority of those I can either show you how to do it in a set-based operation instead or explain why it should be done in your client code rather than on the database. Needing to do a loop in sql is exceeding rare.

Answer 7

almost everything can be done set based, try using a number table

why 20 lines? This is all you need

select *,identity(int, 1,1) as Someid into #temp
from sysobjects

declare @id int, @MaxId int
select @id = 1,@MaxId = max(Someid) from #temp

while @id < @MaxId
begin
-- do your stuff here
print @id
set @id =@id + 1
end

Answer 8

it depends what you want to do in a loop. using a while loop is not difficult at all:

declare @i int
set @i = 20
while @i>0 begin
... do some stuff
set @i = @i-1
end

it only becomes cumbersome when using cursors, which should be avoided anyways.

Answer 9

You might try using user defined functions to do most of the work instead of taking a loop based approach. This would preserve the intention of the SQL language which is set based.