sql loop through each row in a table

Question

I've created a program that generates buy and sell signals of stocks. I've also created logic that tests the different signals and provides the return for each trade.

The next step is to simulate the strategy and its rules over a long period of time. All information is exported to text files and imported to a table in a SQL Server database. I've realized that I need to declare a number of variables such as StartCapital, CurrentCapital, NumberOfPositions, PositionsLeft. One of the columns is named BuyPrice and indicates when to buy and to which price, when this occurs NumberOfPositions should be subtracted by 1.

The column SellPrice indicates when to sell and to which price, when this occurs NumberOfPositions needs to be added by one. The maximum of NumberOfPositions should be 5 and minimum 0. The desired result is to see how the CurrentCapital unfolds.

I would very much appreciate any input and some kind of SQL code to start form.

Can't really picture what you're after, but to answer the question in your subject line, you loop through the rows in a table with a CURSOR. — Tab Alleman, Feb 13 '15 at 19:47
Agreed that your question is very unclear. However, from the pseudo-explanation I would suggest you don't need or want a cursor for this. You should be able to do this with a set based approach. — Sean Lange, Feb 13 '15 at 19:54

score 44 · Answer 1 · answered Mar 13 '17 at 15:07

44

There is another way to loop. I've seen a lot of answers increment the counter by 1. However, in my experience, there is no guarantee that your ids in the dataset won't have gaps.

Here is a solution that I often use:

declare @idColumn int

select @idColumn = min( TableID ) from Table

while @idColumn is not null
begin
    /*
        Do all the stuff that you need to do
    */
    select @idColumn = min( TableID ) from Table where TableID > @idColumn
end

answered Mar 13 '17 at 15:07

Owen

541
4
5

2

Do you need to have the increment statement be a select statement? Or can you simply just set @idColumn = ? – Sep 22 '17 at 16:50
1

@Rfjt No, I don't think it needs to be a select statement. It can be set in whatever way suits your needs. – Owen Sep 25 '17 at 20:32
2

Nice query . Awesome !!! – karthic4info Oct 19 '21 at 13:25
@Owen - Be careful if you are inserting into TableID inside the loop; otherwise, this code will loop forever. – dcp Aug 22 '22 at 20:08
It's O(n^2), not to good. – Yoda Jun 16 '23 at 15:48

Jorge E. Hernández · Answer 2 · 2017-10-09T14:02:13.853

Based on the caption of your question. This is the way I loop through each row of a table using a variable of type TABLE:

DECLARE
    @counter    INT = 1,
    @max        INT = 0

-- Declare a variable of type TABLE. It will be used as a temporary table.
DECLARE @myTable TABLE (
    [Id]        int identity,
    [Column1]   nvarchar(max),
    [Column2]   nvarchar(100)
)

-- Insert your required data in the variable of type TABLE
INSERT INTO @myTable
SELECT Column1, Column2
FROM [dbo].[YOUR_DATABASE_TABLE]

-- Initialize the @max variable. We'll use thie variable in the next WHILE loop.
SELECT @max = COUNT(ID) FROM @myTable

-- Loop 
WHILE @counter <= @max
BEGIN

    -- Do whatever you want with each row in your table variable filtering by the Id column
    SELECT Column1, Column2
    FROM @myTable
    WHERE Id = @counter

    SET @counter = @counter + 1
END

Been ages since I last coded in SQL - thanks for the template — Belfield, Feb 28 '17 at 17:39

sql loop through each row in a table

2 Answers2

Linked