Error when creating a SQL script

Question

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?

This of course is something that should not be done in a cursor. — HLGEM
– HLGEM, Commented Nov 24, 2010 at 15:57

Joe Stefanelli · Accepted Answer · 2010-11-24 15:50:38Z

1

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

answered Nov 24, 2010 at 15:50

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shiva · Accepted Answer · 2010-11-24 15:54:05Z

0

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

answered Nov 24, 2010 at 15:54

shiva

1

Comments

HLGEM · Accepted Answer · 2010-11-24 16:00:49Z

0

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

answered Nov 24, 2010 at 16:00

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

Error when creating a SQL script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related