Foreach in SQL to put a value in a variable
My problem is that I have a subquery that works perfectly, but for this I have to enter the id I want in the variables.
In variable @ mediaKM_mes I make select to table tblabastiamento by car id; in variable @kmInicio I do selelect to table tblcar by car id; and in @kmFeitos I do again select the table tblabastiamento by the id of the car;
For my query to be able to populate the table as I structured it needed pass the id_Carro that is entered to a variable @id_carro to get the other variables to fetch the desired information.
What happens to me is that I have to be the one to put the number and only fill in with either id 15 or id 21
First I declared the variables like this:
@ id_car which is where I wanted to save the value of the car id that appears on each row;
@mediaKM_mes is the average km the car does per month and to tell which is the car would use the variable @id_car;
@kmInicio is the initial Km the car had when it was registered;
@kmFeitos which is the sum of the km made by this same car to sum with the @kmInicio and get to 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
2 answers
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 foreach you can do something like 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
You must set a cursor if you need to loop through the records of your query. 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
I have an example in mysql the logic is the same, it 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
Notice in SELECT BALANCE_DETAIL_PUBLISHER I am already using the variable ID that is receiving the value of the column ID from Table affiliate .
Adapt the example to your sql-server DBMS and needs, I hope this example will give you the understanding. Hugs.