Stored procedure response (SQL)

Question

Stored procedure response (SQL)

Navigation

#1 by (0 votes)

1

I am trying to do a procedure stored in SQL for a restaurant sales system that when adding a dish to a sale check if there is stock of the ingredients available and return a response 1 if there is or 0 if there is not.

ALTER proc [dbo].[check_stock_venta] 
@Idplato int,
@Cantidad int,
@Rpta int output
as
i.Stock;t.Cantidad from Insumos i join (select 
dp.id_insumo,Cantidad=sum(dp.Cantidad*@Cantidad) from Detalles_plato dp 
where dp.id_plato=@Idplato group by dp.id_insumo)t on i.Idinsumo=t.id_insumo

At the moment I have done a join of the tables to have the fields I need, I had thought about using an if with the fields

i.Stock
t.Cantidad

and if this if returned the answer if the stock was greater than or equal to the amount, I do not know if it is the best way to do this and on the other hand I do not know how to use these two fields after doing the join of the tables .

I leave a link where you can see how tables are made and related .

sql sql-server

asked by Andres Cabrera 27.12.2016 в 00:22

source

1 answer

Android: Implement Glide Librarian Symbol 'k' at (1) has not IMPLICIT type (Fortran g95)

score 0 · Accepted Answer

If there is more stock than the amount needed for the dish returns 1, if it does not return 0, I hope it serves you.

Select CASE WHEN i.Stock >= t.Cantidad THEN 1 ELSE 0 END resultado 
from Insumos i join (select dp.id_insumo,Cantidad=sum(dp.Cantidad*@Cantidad) 
from Detalles_plato dp 
where dp.id_plato=@Idplato group by dp.id_insumo)t on i.Idinsumo=t.id_insumo

EDIT:

We are left with the minimum result of the list of ingredients, if you return 1 all the ingredients have stock, but some returned 0 and is missing stock of at least one ingredient.

select MIN(resultado) from (
Select CASE WHEN i.Stock >= t.Cantidad THEN 1 ELSE 0 END resultado 
from Insumos i join (select dp.id_insumo,Cantidad=sum(dp.Cantidad*@Cantidad) 
from Detalles_plato dp 
where dp.id_plato=@Idplato group by dp.id_insumo)t on i.Idinsumo=t.id_insumo
) as tt

Greetings,