help, stored procedures and cursors

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 erro开发者_JAVA技巧rs 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??

Others have suggested using DEALLOCATE. The problem with that is that, in some error situations, it won't be called. If you then attempt to use the same connection to call this stored proc, the cursor will still be allocated.

I'd prefer to instead declare the cursor as LOCAL, which means it's automatically deallocated when the stored proc is exited (whether normally or not).

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)

After

close sinallages 

You need to call

deallocate sinallages 

Have a look at DEALLOCATE (Transact-SQL)

you have to dealocate the cursor after closing it:

DEALLOCATE sinallages

http://msdn.microsoft.com/en-us/library/ms188782.aspx