In Oracle, we can use
FOR employee_rec in (select id from mytbl)
LOOP
DBMS_OUTPUT.PUT_LINE(employee_rec.id);
END LOOP;
In SQL Server, do we have something similar?, if not, how to do this loop?
-
Check out this answer: stackoverflow.com/questions/3557243/…Jubal– Jubal2012-06-29 15:42:15 +00:00Commented Jun 29, 2012 at 15:42
-
Oracle is better optimzed for looping than SQl Server is, you need to start thinking terms of processing whole sets not one-record at a time.HLGEM– HLGEM2012-06-29 15:52:26 +00:00Commented Jun 29, 2012 at 15:52
Add a comment
|
1 Answer
It's a little more verbose in SQL Server, if you want to do it right:
DECLARE @id INT;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT id FROM mytbl;
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @id;
FETCH NEXT FROM c INTO @id;
END
CLOSE c;
DEALLOCATE c;
However, usually we tend to avoid loops and cursors, since a relational database excels at set-based operations (in almost cases). There are exceptions of course, like any rule, but if I had to guess I would suspect that you don't need to loop to do what you are trying to do. What are you really trying to do? Why do you need to process each row individually?
2 Comments
user595234
thanks. Do we have more concise way ? you see, Oracle only have 4 lines.
Aaron Bertrand
If this is a reason for choosing Oracle over SQL Server, stick with Oracle. You can make this a little shorter in T-SQL, or you can throw yourself into the world of EF / Linq where you can write very concise and inefficient loops all you want. The real answer, as I was trying to impress upon you, is that thinking about this as a loop in the first place is flawed. Try to solve the problem "What are you trying to do to the set" rather than "What are you trying to do to each row." Turn to a cursor only when the set-based method is too complex or has inherent performance problems.