CODEWITHSUNDEEP
sql-serveroracle-databasefunctionloopswhile-loop
Joel Jacobson
Joel Jacobson

Reputation: 145

Loops and Cursors in Oracle into Sql Server

I have a block of code for a function trying to convert it from Oracle to Sql Server:

Oracle Code:

    If nDaysAfter = 0 then

    while (IsHoliday(dNextDay) = 1) or (RTrim(To_Char(dNextDay, 'DAY')) in ('SATURDAY','SUNDAY')) Loop
    dNextDay := dNextDay + 1;
  End Loop;

  RETURN dNextDay;

Else   dNextDay := dNextDay + 1;
  For i in 1..nDaysAfter Loop

    while (IsHoliday(dNextDay) = 1) or (RTrim(To_Char(dNextDay, 'DAY')) in ('SATURDAY','SUNDAY')) Loop
      dNextDay := dNextDay + 1;
    End Loop;
    dNextDay := dNextDay + 1;

  End Loop;
  RETURN dNextDay - 1;
  End if;

Sql Server code:

    If @nDaysAfter = 0 begin

   while (IsHoliday(@dNextDay) = 1) or (RTrim(convert(char,@dNextDay, 'DAY')) in ('SATURDAY','SUNDAY')) Begin
    SET @dNextDay = @dNextDay + 1;
  End;

 RETURN @dNextDay;

End
Else BEGIN 
SET @dNextDay = @dNextDay + 1;
Declare i CURSOR for 1..nDaysAfter Loop

while (IsHoliday(@dNextDay) = 1) or (RTrim(convert(char,@dNextDay, 'DAY')) in ('SATURDAY','SUNDAY')) Begin
      SET @dNextDay = @dNextDay + 1;
    End;
    SET @dNextDay = @dNextDay + 1;

  Fetch i INTO;
  End;
  Close i;
  Deallocate i;
  RETURN @dNextDay - 1;
  End

I am having issues converting this line: Declare i CURSOR for 1..nDaysAfter Loop it highlight under number 1. (nDaysAfter is declare as Float) Thank I will appreciate as much help possible.

Upvotes: 1

Views: 672

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

I'd replace the cursor in the SQL server code with a WHILE loop.

declare @i int = 1
while @i <= nDaysAfter
begin
    while (dbo.IsHoliday(@dNextDay) = 1) or (RTrim(convert(char,@dNextDay, 'DAY')) in ('SATURDAY','SUNDAY')) 
    Begin
      SET @dNextDay = @dNextDay + 1;
    End
      SET @dNextDay = @dNextDay + 1;

  set @i = @i+1
End

  RETURN @dNextDay - 1;

Upvotes: 1

Related Questions