How do we use 2 column's difference value for TOP query in sql

Question

I want to generate N number of rows from Table A. where N is difference of column2- column1 from Table A for all the records in the table.

I don't want to use loops for this.

Sample Query

Select TOP (tbl1.Column2 - tbl1.Column1) tbl1.Column3, tbl2.Column1 
from TableA tbl1 cross join
     TableB tbl2
where tbl1.ID= 10

but this gives me below error

The reference to column "Column2" is not allowed in an argument to a TOP, OFFSET, or FETCH clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.

What is the alternate to do this?

Answer 1

Normally, you would use TOP with ORDER BY.

In any case, you can do what you want using window functions:

select column3, column1
from (select (tbl1.Column2 - tbl1.Column1) as numrows,
             tbl1.Column3, tbl2.Column1,
             row_number() over (order by (select null)) as seqnum 
      from TableA tbl1 cross join
           TableB tbl2
      where tbl1.ID = 10
     ) t
where seqnum <= numrows;