Create Precedent to adjust price as per conditions (SQL Server)

Question

In a comic book of a bookstore need to create a precedent to readjust the price column according to the percentage and gender reported (using transaction control). Like I should do?

create table livro (
liv_ID      int not null constraint PKcod_livro primary key,
aut_ID          int not null,
gen_ID      int not null,
liv_Titulo  varchar(100) not null,
liv_Preco   decimal(10,2) not null,
constraint  fkLivAut foreign key (aut_Id) references autor(aut_Id),
constraint  fkLivGen foreign key (gen_Id) references genero(gen_Id));

 

create table genero (
gen_ID  int not null constraint PKGenero primary key,
gen_Nome varchar(50) not null );

Answer 1

Considering that the readjustment parameter is reported as a real numerical value. For example, if the adjustment is 12.5%, the value passed as parameter is in the form 12.5

To calculate the new book price, the percentage of the adjustment is divided by 100 and then added to 1. The current book price is then multiplied by the result of this operation.
For example:

  12,5 / 100 = 0,125
  Fator de multiplicação = 1 + 0,125 = 1,125

If the current price of the book is $ 122,00, then we have

  Novo preço do livro = R$ 122,00 * 1,125 = R$ 137,25

If the gender parameter is given as code (numeric value and integer), we have:

-- código #1 v3 -- gênero é informado como código numérico
CREATE PROCEDURE Atualiza_Preço 
                 @pGênero int, 
                 @pPercentual decimal (5,2)
as
begin
declare @Fator decimal (10,5);
set @Fator= (1 + (@pPercentual / 100));

BEGIN TRANSACTION;

UPDATE livro
   set liv_Preco*= @Fator
   where gen_ID = @pGênero

COMMIT;

end;
go

---

But if the gender parameter is informed as denomination (text), we have

-- código #2 v3 -- gênero é informado como texto
CREATE PROCEDURE Atualiza_Preço 
                 @pGênero varchar(50), 
                 @pPercentual decimal (5,2)
as
begin
declare @Fator decimal (10,5);
set @Fator= (1 + (@pPercentual / 100));

BEGIN TRANSACTION;

UPDATE L
   set liv_Preco*= @Fator
   from livro as L
        inner join genero as G on G.gen_ID = L.gen_ID
   where G.gen_Nome = @pGênero

COMMIT;

end;
go