I am new to stored procedures, and i am trying to create a procedure
to check pin validation, and also create count for amount of times a given PIN is used.
the database values are;
id;
Pin_no;- these holds the PIN numbers.
Reg_num; - These holds the registration number of the specified PIN user.
P_Session; - these holds the school session PIN number was used.
Program; - these holds the school program PIN number was used.
Tries; - these holds the amount of times PIN has been used and if more than 3 times PIN row deletes.
i have designed this code as below . pls help ;
 
 
  1. CREATE PROCEDURE [dbo].PinValidation  
  2.     @Pin_no varchar(24),  
  3.     @Reg_Num varchar(24),  
  4.     @Session varchar(50),  
  5.     @Tries INT,  
  6.     @Program varchar(50)  
  7. AS  
  8. begin  
  9.     SELECT Pin_no FROM PIN  
  10.     WHERE Pin_no=@Pin_no  
  11.     if @Reg_Num is not Null  
  12.     BEGIN  
  13.     IF NOT EXISTS (SELECT Reg_Num FROM PIN WHERE Reg_Num=@Reg_Num and Program=@Program and P_Session=@Session)  
  14.     BEGIN  
  15.     UPDATE PIN  
  16.     SET Tries= Tries+1  
  17.     WHERE Reg_Num=@Reg_Num--incrases the value of tries by 1 each time pin is used  
  18.     SELECT @Tries[Tries]  
  19.     end  
  20.     ELSE  
  21.     UPDATE PIN  
  22.     SET Reg_num = @Reg_Num, P_Session=@Session, Tries=Tries+1, Program=@Program  
  23.     Where Pin_no=@Pin_no --Updates database pin value for used pin on first usage  
  24.      end  
  25.      end  
  26.      If @Tries is not Null   
  27.      BEGIN  
  28.     if NOT EXISTS (SELECT Pin_no FROM PIN WHERE Pin_no=@Pin_no)  
  29.      BEGIN  
  30.      DELETE FROM [PIN ]  
  31.      WHERE  (( [Tries] = 3))--Check the Amount of times pin has been used if >3 delete entire pin row  
  32.      end  
  33.      end  
  34.      ELSE  
  35.      BEGIN  
  36.      SELECT -1  
  37.      END