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.

Answer 1

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

Answer 2

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