'int' is not a recognized CURSOR option.

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

While Loop in SQL Server

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

How to create a SQL Function that Returns a Table

SQL Create Table Valued Function (SQL SERVER 2008) / How to Create SQL_TABLE_VALUED_FUNCTION

The following snippet uses a Table variable to create a table and inserts value into it, which the function returns

CREATE FUNCTION ReturnATable()
RETURNS @TabVar TABLE
(
 OrderID int not null,
 OrderDate datetime,
 OrderStatus bit,
 OrderValue decimal,
 BilledBy varchar(20)
)
AS
BEGIN
DEclare @Date datetime ;
Set @Date= GETDATE();
Insert Into @TabVar values
(21, @Date , 1, 212.42, 'Jose');
return 
END

The function can be executed as shown below

Select * from ReturnATable()