Foreach in SQL to put a value in a variable

Navigation
0

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

In the @mediaKM_mes variable I select the tbl table by car id; in the @km variable I select the table tblCar by the car id; and in the @kmFeitos I do again select to the table tblAbstacimento by the id of the car;

In order for my query to fill the table as struct I needed to pass the inserted car_id to a @id_carro variable 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 fills either with id 15 or with id 21

FirstIdeclarethevariablesthisway:

@id_carrowhichiswhereyouwantedtostorethevalueofthecaridthatappearsoneachline;

@mediaKM_mesistheaveragekmthatthecarmakespermonthandtosaywhichcarwouldusethevariable@id_carro;

@kmStartistheinitialKmthatthecarhadwhenitwasregistered;

@kmFeitosthatisthesumofkmmadebythissamecartoaddwiththe@kmStartandgetthecurrentkm.

--VARIÁVEISdeclare@id_carroasint;declare@mediaKM_mesasdecimal=(select--nv3round(AVG(cast(kmMesasfloat)),0)asmediaKmMesfrom(select--nv2marca,mes,sum(kmfeitos)askmMes,sum(valorReal)aspagoMes,round(cast(avg(media)asfloat),1)asmediaMesfrom(select--nv1*,casewhenLitros=0orkmfeitos=0thennullelsecast(((Litros/kmfeitos)*100)asdecimal(18,1))endasmediafrom(selectc.marcaasmarca,a.KmFeitosaskmfeitos,MONTH(data)asmes,valorPago-ValorDescontoasvalorReal,cast(valorPago/precoLitroasdecimal(18,2))asLitrosfromtblAbastecimentoajointblCarrocona.id_carro=c.id_carrowherec.id_carro=@id_carro)asnova1)asnova2groupbymes,marca)asnova3groupbymarca);declare@kmInicioasdecimal;set@kmInicio=(selectkm_iniciofromtblCarrowhereid_carro=@id_carro)declare@kmFeitosasdecimal;set@kmFeitos=(selectsum(KmFeitos)fromtblAbastecimentowhereid_carro=@id_carro)--CÓDIGOselect--nv3id_Carro,id_despesa,marca,descricao,v,km,cast(v/y_Mesesasdecimal(18,2))asvalorMensal,casewheny_Meses>12theny_Meses-12endasmesPrevistofrom(select--nv2*,cast(Revisao/@mediaKM_mesasdecimal(18,0))asy_Mesesfrom(select--nv1*,km-(@kmInicio+@kmFeitos)asRevisaofrom(selectc.id_carroasid_Carro,d.id_despesaasid_despesa,c.marcaasmarca,d.descricaoasdescricao,kmaskm,valorasvfromtblPrevistaCarropcjointblCarroconpc.id_carro=c.id_carrojointblDespesadonpc.id_despesa=d.id_despesawherec.id_carro=@id_carro)asnv1)asnv2)asnv3

    
asked by anonymous 11.07.2017 / 01:05

2 answers

3

I do not 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 link 

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
    
11.07.2017 / 18:27
0

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

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 the SELECT BALANCA_DETAIL_PUBLISHER I am already using the variable ID that is receiving the value of the column ID of the AFFILIATE table .

Adapt the example to your sql-server DBMS and needs, I hope this example gives you the understanding. Hugs.

    
11.07.2017 / 03:13
Passing an Array of Strings to a preparedStatement? Function to change Height with errors