Foreach in SQL to put a value in a variable

Asked

Viewed 7,693 times

0

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

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

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

inserir a descrição da imagem aqui

  • 1

    You can detail the calculations you want to perform?

  • 1

    @Diogosousa: To evaluate whether or not the use of cursor is necessary, let us know what is the purpose of the code you want to develop as well as the calculations you need to obtain.

  • @I edited my question in an attempt to be more enlightening. Imagine that I have three cars, each car had different kilometers, different averages, and what I wanted to do was in each line to know what car it was, put inside a variable so that the other variables could get the information needed to perform the calculations. Thank you

  • José Diz I have put more information, I hope not to be too confusing, but as I said, my logic is: imagining that I have 3 cars, each car with different kilometers, different averages etc, and what I wanted to do was in each line to know what car it is, put inside a variable so that the other variables could get the information needed to perform the calculations. Thank you

  • Diogo, why doesn’t my example cursor suit you? I copied from an advertising system of mine built in 2012 and that until today every day makes the closing box, therefore because I received vote against?

  • @Diogosousa: What I would like to know is: (a) what is the problem to be solved? (b) What result should the query return? // Other questions: (c) Which tables are involved? (d) Which columns of the tables contain useful information for the query to be mounted? // I could add this information in the text of the topic, but without making any mention of the code you posted?

  • @José Diz thanks for the help, I have already edited my problem... I have some difficulty in describing it, but I think it has improved a bit. Thank you so much for your help.

  • @Diogosousa: The questions I asked in the previous comment should be considered in a scenario where no single line of code has been written. That there is neither the code nor the variables. The beginning. That moment when there are only the tables. // The impression I have is that a new code will have to be written, from scratch.

  • @José Says what he thinks I should improve ?

Show 4 more comments

2 answers

3


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
  • Actually I do not know what solution to use, if the "in" of this had no problem in using it

  • your code works, but it generates 3 different tables and you needed it to be all in one to put it in a datagridview, it’s possible?

  • The part after -- YOUR COODIGO INSIDE FOP Must be uploaded by your code, I put select only to show that the loop is made for each of the id_carro. To put everything in one table. VC can create another temporary table and insert the values you want into it. After the end you give a select in this table.

  • thank you very much, I created the temporary table and entered the data in it and it turned out, finally my problem is solved and you were very effective and very direct, thanks again

0

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.

  • I’m sorry for the vote against, I must have clicked and I didn’t notice, but I already corrected... I tried the code but it gives me some errors for example in FOUND_ROWS(), always turns red and was trying to solve before giving some feedback, as soon as I can (or not) I’ll get back to you... thank you very much !

  • Opa Di I will check if I copied something wrong! But from what I understand you are using the correct SGBD sql-server ? I posted that well-functional routine piece of stored that I built makes the box lock of an advertising system that works by clicks but is mysql! Tell me your DBMS maybe I can mount an interator cursor on sql-server.

  • yes my DBMS is sql-server. I did something similar that worked, but it shows in separate tables and wanted it all in one. It’s not easy

Browser other questions tagged

You are not signed in. Login or sign up in order to post.