Create SQL Server procedures

Question

Create SQL Server procedures

Good afternoon everyone! :)

I'm new to programming and I'm having some difficulties

I am trying to create a procedure to update the column Cli_TotalCompras, with the total amount already spent by the customer. That is, with the total amount of purchases already made, by each customer.

I already did the select, and I took the total amount for each client. But I could not create the procedure to enter this value in the column Cli_TotalCompras.

Could anyone give me an example, how can I solve this problem?

I think my logic is correct (to select this total value of the first client, to make an insert in the Cli_TotalCompras column of this first client and after that to do these steps from the second client to the last through a loop), but I can not reproduce in the code.

I thank everyone who can help me!

create procedure sp_Cli_TotalCompras
@cli_id int,
as
begin
select c.cli_id, sum(vp.vpr_valorunit)
from vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
inner join cliente c on v.cli_ID = c.cli_ID
where c.cli_ID = @cli_id
group by c.cli_id
end

0

sql-server stored-procedures

Author: Juliana Camargo, 2020-05-16

Source

1 answers

score 1 · Answer 1

Just join the two commands, it can be done like this:

INSERT INTO NomeDaTabela (cli_id, Cli_TotalCompras)
SELECT c.cli_id, SUM(vp.vpr_valorunit) Cli_TotalCompras
  FROM vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
 INNER join cliente c on v.cli_ID = c.cli_ID
 WHERE c.cli_ID = @cli_id
 GROUP by c.cli_id

Just put the correct name of the table and also the field with the Client id, and other fields you need in insert

As a procedure, another way would be to store the sum in a variable and then use:

DECLARE @soma float
SET @soma = (SELECT SUM(vp.vpr_valorunit) 
  FROM vendaProduto vp inner join venda v on vp.ven_ID = v.ven_ID
 INNER join cliente c on v.cli_ID = c.cli_ID
 WHERE c.cli_ID = @cli_id
 GROUP by c.cli_id)

INSERT INTO NomeDaTabela (cli_id, Cli_TotalCompras) VALUES (@cli_id, @soma)

In this case, set the type of the variable equal to the type of the field in the INSERT, here I used float to exemplify, but it could be decimal or another type.