CODEWITHSUNDEEP
sql-servert-sql
delete
delete

Reputation:

Trouble with this simple stored procedure

create procedure sp_DescuentoCategoriaInvierno
as
declare @IDProductoOfertado int, @ProductoNombre nvarchar(256), @CategoriaNombre nvarchar(256), @SubcategoriaNombre nvarchar(256), @Precio float

declare cursorProducto cursor for
select o.IDProducto, p.Nombre, c.Nombre, s.Nombre, o.Precio from OfertaProducto as o
inner join Producto as p on o.IDProducto = p.ID
inner join Subcategoria as s on p.IDSubcategoria = s.ID
inner join Categoria as c on s.IDCategoria = c.ID
order by p.Nombre

open cursorProducto
    fetch next from cursorProducto into @IDProductoOfertado, @ProductoNombre, @CategoriaNombre, @SubcategoriaNombre, @Precio
    while @@FETCH_STATUS = 0
        begin
            if(@CategoriaNombre='Invierno')
            begin           
                select @Precio --TROUBLE IS HERE. 
                from OfertaProducto --WHAT SHOULD I DO?
                update OfertaProducto set Precio = @Precio * 0.5                
            end
            fetch next from cursorProducto into @IDProductoOfertado, @ProductoNombre, @CategoriaNombre, @SubcategoriaNombre, @Precio
        end

close cursorProducto
deallocate cursorProducto

This one is simple enough, I'm just trying to have every OferredProduct in my dabase that has a Category of 'invierno' have a reduced price:

Here's the model:

alt text

So what I'd like it to iterate through each OfferedProduct, if it has a category of 'Invierno' reduce the price of it to 50%. I'm missing something small I'm sure. :P

Thanks!

Upvotes: 0

Views: 95

Answers (3)

Lamak
Lamak

Reputation: 70678

As Jeff said before, you don't need a cursor for this, it would be better just an UPDATE statement for this. Try something like this:

UPDATE o
SET o.Precio = o.Precio * 0.5
from OfertaProducto as o
inner join Producto as p on o.IDProducto = p.ID
inner join Subcategoria as s on p.IDSubcategoria = s.ID
inner join Categoria as c on s.IDCategoria = c.ID
WHERE c.Nombre = 'Invierno'

Upvotes: 2

Jeff Hornby
Jeff Hornby

Reputation: 13680

You don't need the select statement but you do need a where clause on your update statement:

update OfertaProducto set Precio = @Precio * 0.5
where IDProducto=@IDProductoOfertado

Note that this could be accomplished better with a single UPDATE statement acting on the entire set at once

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121067

I don't understand this:

select @Precio 
from OfertaProducto 
update OfertaProducto set Precio = @Precio * 0.5

@Precio is already selected through the cursor, so why do you select it again. And you also need to give an id in the update statement. Otherwise all rows wlil be updated. I think this would do it:

update OfertaProducto set Precio = @Precio * 0.5 where IDProducto = @IDProductoOfertado

Upvotes: 0

Related Questions