help, stored procedures and cursors

Question

i have to write a stored procedure, where you give the month, and a credit card number, and it calulates 1% for each transaction made in the first 10 days of the month, 2% for transactions between 10 and 20, and 3% for transactions above 20. And i must use cursors.

i wrote this code, but i get some errors when i try to run the precedure

   create procedure cardP
   /* month ex 1,3,5 etc*/
   @minas int,
   @cardNo bigint



as
/* creating the cursor*/
DECLARE sinallages CURSOR
FOR SELECT cc_number,day([DateTime]),charged_amount FROM transactions
where cc_number=@cardNo and month([DateTime])=@minas

/* declaring local variables*/
declare @poso int,@karta bigint,@mera int,@pos float,@pos2 float,@pos3 float,
@count int,@counter int

open sinallages



set @count=(select count(cc_number) from transactions where cc_number=@cardNo and month([DateTime])=@minas )
/* get 1st row*/
fetch sinallages into @karta,@mera,@poso
while (/*@@sqlstatus != 2*/@counter<@count)
  begin
    if day(@mera)<=10
    set @pos =@poso+ @poso * 0.01
    else
    if day(@mera)>10 and day(@mera)<=20
    set @pos2 =@poso+ @poso * 0.02
    else
    if day(@mera) > 20 
    set @pos3 =@poso+ @poso * 0.03
    fetch sinallages into @karta,@mera,@poso
  set @counter=@counter+1
  end

close sinallages

return

when i call the procedure i get

EXEC cardP @minas = 5, @cardNo =4929569752542450

Msg 16915, Level 16, State 1, Procedure cardP, Line 20
A cursor with the name 'sinallages' already exists.
Msg 16922, Level 16, State 1, Procedure cardP, Line 31

Cursor Fetch: Implicit conversion from data type datetime to int is not allowed.

thank you :) i now deallocate the cursor at the end of the stored procedure and removed the day(). Now i want to print the pos+pos2+pos3. I use print pos+pos2+pos3 but it doesnt print anything. why is that ??

    ................
      set @counter=@counter+1
  end
print @pos+@pos2+@pos3
close sinallages



return 
DEALLOCATE sinallages;

it seems like hte variables po,pos2,pos3 are left null??

Answer 1

Yes, you need to deallocate the cursor after closing it. Besides, if your query had an error before closing the cursor, it may have stayed open, so i recommend you execute the CLOSE and DEALLOCATE before executing your procedure again. For your second error, you are using the function DAY() over a variable of type INT, change if day(@mera)<=10 with if @mera<=10. Update: Now that you fixed the problems you had, when you add each @pos variable, following your logic, one of them is always null, so you should add them like this: Print isnull(@pos1,0)+isnull(@pos2,0)+isnull(@pos3,0)