Create procedure to adjust price according to conditions (SQL Server) [closed]

Question

Create procedure to adjust price according to conditions (SQL Server) [closed]

Navigation

#1 by (2 votes)

1

In a DB of a bookstore I need to create a procedure to readjust the price column according to the percentage and gender reported (using transaction control). How should I do it?

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 );

sql sql-server procedure

asked by anonymous 26.10.2016 / 23:43

1 answer

Control of modal appearance How to write data in webstorage without replacing the data already recorded? [duplicate]

score 2 · Answer 1

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

To calculate the new price of the book, the percentage value of the adjustment is divided by 100 and then added to 1. The current price of the book 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 entered as code (numeric and integer value), 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 given 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