Procedure loop in Oracle to SQL Developer-CodePudding

I have a procedure which is written using Oracle database and now I want to migrate it to SQL Server but I am having an issue when it comes to loop. As far as I know, SQL Server doesn't have "FOR" loop, it only has "WHILE" loop. The code is :

FOR something in columns 
LOOP
PRINT('SOMETHING');
PRINT('SOMETHING');
END LOOP;

I want to convert it to SQL Server syntax but I have a problem when it comes to MS SQL syntax.

CodePudding user response：

You could use cursor:

DECLARE @col_name VARCHAR(128);
DECLARE @col_value VARCHAR(128);

-- declare cursor 
DECLARE columns CURSOR FOR
  SELECT something, other_thing
    FROM table_list;

-- open cursor
OPEN columns;
 
-- loop through a cursor
FETCH NEXT FROM columns INTO @col_name, @col_value;
WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT CONCAT('SOMETHING: ', @col_name, ' / OTHER_THING: ', @col_value);
    FETCH NEXT FROM columns INTO @col_name, @col_value;
  END;
-- close and deallocate cursor
CLOSE columns ;
DEALLOCATE columns;