CODEWITHSUNDEEP
sql-servert-sql
delete
delete

Reputation:

Error when creating a SQL script

Here's the script:

create procedure sp_DescuentoAlquiler
as
declare @IDAlquiler int, @NumeroPelicula int, @MontoTotal float

declare cursorAlquiler cursor for
select a.ID, count(d.ID) as @NumeroPelicula 
from Alquiler a inner join DetalleAlquiler d on a.ID = d.IDAlquiler
group by a.ID

open cursorAlquiler
    fetch next from cursorAlquiler into @IDAlquiler, @NumeroPelicula
    while @@FETCH_STATUS = 0
        begin
            if(@NumeroPelicula >= 3)
            begin
                select @MontoTotal = SUM(d.PrecioAlquiler)
                from DetalleAlquiler d where d.IDAlquiler = @IDAlquiler
                update Alquiler set MontoTotal = @MontoTotal * 0.3
                where ID = @IDAlquiler
            end
            fetch next from cursorAlquiler into @IDAlquiler, @NumeroPelicula
        end

close cursorAlquiler
deallocate cursorAlquiler

I'm getting an error in Line 6 after count(d.ID), on @NumeroPelicula:

Msg 102, Level 15, State 1, Procedure sp_DescuentoAlquiler, Line 6 Incorrect syntax near '@NumeroPelicula'.

Any suggestions?

Upvotes: 0

Views: 77

Answers (3)

HLGEM
HLGEM

Reputation: 96570

I'd need data examples to be sure (anf thorough testing) but this seems as if it might do the job in a set-based manner. Cursors are a very bad choice for this kind of processing due to performance problems especially as the data set gets large.

   update A 
   set MontoTotal = sum(d.PrecioAlquiler) * 0.3 
   From Alquiler A
   join (select a.ID, count(d.ID) as NumeroPelicula  
            from Alquiler a inner join DetalleAlquiler d on a.ID = d.IDAlquiler 
            group by a.ID ) b
    on a.id = b.id
   JOIN DetalleAlquiler d 
    ON d.IDAlquiler = b.ID
   where b.NumeroPelicula  >=3

Upvotes: 0

shiva
shiva

Reputation: 1

Try removing the @ symbol below

declare cursorAlquiler cursor for 
select a.ID, count(d.ID) as @NumeroPelicula

should be 

declare cursorAlquiler cursor for 
select a.ID, count(d.ID) as NumeroPelicul

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Remove the @ from the column alias for your count.

select a.ID, count(d.ID) as NumeroPelicula 
from Alquiler a inner join DetalleAlquiler d on a.ID = d.IDAlquiler
group by a.ID

Upvotes: 1

Related Questions