Foreach in SQL to put a value in a variable

Question

My problem is that I have a subquery that works perfectly, but for that I have to insert the id I want in the variables.

In the variable @mediaKM_mes I select to the table tblAbastecimento by the car id; in the @kmInicio variable I select to the table tblCarro by the car id; and in @kmFeitos do again select to table tblAbastecimento by car id;

In order for my query to be able to fill the table as I structured it I needed to pass the id_Carro that was inserted to a @id_carro variable to get the other variables to fetch the desired information.

What happens to me is that I have to put the number and only fill in either with id 15 or id 21

First I declared the variables this way:

@id_car which is where you wanted to store the value of the car id that appears on each line;

@mediaKM_mes is the average km the car makes per month and to tell which car would use the variable @id_car;

@kmInicio is the initial Km that the car had when it was registered;

@kmFides that is the sum of the km made by that same car to add with the @kmInicio and get the current km.

-- VARIÁVEIS

declare @id_carro as int;

declare @mediaKM_mes as decimal = ( select -- nv3 
round( AVG( cast( kmMes as float)) ,0) as mediaKmMes from( select -- nv2
    marca,
    mes,
    sum(kmfeitos) as kmMes,
    sum(valorReal) as pagoMes,
    round( cast( avg(media) as float),1)as mediaMes from( select -- nv1
    *,
    case when Litros = 0 or kmfeitos = 0 then null else
    cast ((( Litros / kmfeitos) * 100) as decimal(18,1)) end as media from( 
select  c.marca as marca,
    a.KmFeitos as kmfeitos,
    MONTH(data) as mes, 
    valorPago - ValorDesconto as valorReal, 
    cast(valorPago / precoLitro as decimal(18,2)) as Litros from 
tblAbastecimento a join tblCarro c on a.id_carro = c.id_carro where 
c.id_carro = @id_carro) as nova1) as nova2 group by mes, marca) as nova3 
group by marca);

declare @kmInicio as decimal;
set @kmInicio = (select km_inicio from tblCarro where id_carro = @id_carro)

declare @kmFeitos as decimal;
set @kmFeitos = (select sum(KmFeitos) from tblAbastecimento where id_carro = 
@id_carro)


-- CÓDIGO

select -- nv3
    id_Carro,
    id_despesa,
    marca,
    descricao,
    v,
    km,
    cast(v / y_Meses as decimal(18,2)) as valorMensal,
    case when y_Meses > 12 then y_Meses - 12 end as mesPrevisto 
from( 
select -- nv2
    *,
    cast(Revisao / @mediaKM_mes as decimal(18,0))  as y_Meses
    from( select -- nv1
    *,
    km - (@kmInicio + @kmFeitos) as Revisao  from( select   c.id_carro as 
id_Carro,
    d.id_despesa as id_despesa,
    c.marca as marca,  
    d.descricao as descricao, 
    km as km, 
    valor as v from tblPrevistaCarro pc join tblCarro c 
    on pc.id_carro = c.id_carro join tblDespesa d on
    pc.id_despesa = d.id_despesa where c.id_carro = @id_carro) as nv1) as 
    nv2)as nv3

Answer 1

I don’t understand why you need to use a foreach in this case, if you put everything in a query you can use an in.

Anyway if you really want to use the foreach can do something similar to the answer https://stackoverflow.com/a/18514429/8099197

declare @temp_cars TABLE (IDCARRO INT, DESC_CARRO VARCHAR(10))

INSERT INTO @temp_cars
SELECT 1,'BMW' UNION
SELECT 2, 'AUDI' UNION
SELECT 3, 'FUSCA'

DECLARE @LOOP int = 0
WHILE(1 = 1)
BEGIN
  SELECT @LOOP = MIN(IDCARRO)
  FROM @temp_cars WHERE IDCARRO > @LOOP
  IF @LOOP IS NULL BREAK
  declare @id_carro as int;
  SET @id_carro = @LOOP

  -- O SEU COODIGO DENTRO DO FOP
  SELECT * FROM @temp_cars WHERE IDCARRO=@id_carro
END

Answer 2

You must set a cursor if you need to loop through your querie’s records. To do this set a cursor with your query and the variables where the table attributes will be stored at each new increment of the loop, using the INTO operator ("inside")

I have an example in mysql the logic is the same, follows:

BEGIN 
    DECLARE ID        INT DEFAULT 0; 
    DECLARE num_rows  INT DEFAULT 0;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE CURSOR_AFILIADO CURSOR FOR SELECT ID_AFILIADO FROM AFILIADOS; 

    OPEN CURSOR_AFILIADO;
       SELECT FOUND_ROWS() INTO num_rows;
       CURSOR_LOOP: LOOP 
           FETCH CURSOR_AFILIADO 
           INTO ID;

           IF no_more_rows THEN
              CLOSE CURSOR_AFILIADO;
             LEAVE CURSOR_LOOP;
          END IF;

            SELECT 
               COALESCE(SUM(SALDO),0) INTO CREDITO_MENSAL
            FROM     BALANCA_DETALHAMENTO_PUBLISHER 
            WHERE                                 
               ID_AFILIADO         = ID 

          SET loop_cntr = loop_cntr + 1;

      END LOOP  CURSOR_LOOP;
 END

Note the SELECT BALANCA_DETALHAMENTO_PUBLISHER I am already using the ID variable that is receiving the table ID column value AFFILIATE.

Adapt the example to your sql-server DBMS and needs, hopefully this example will give you the understanding. Hugs.