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 ; CREATE PROCEDURE [dbo].PinValidation @Pin_no varchar (24) @Reg_Num varchar (24) @Session varchar (50) @Tries INT @Program varchar (50) AS begin SELECT Pin_no FROM PIN WHERE Pin_no=@Pin_no if @Reg_Num is not Null BEGIN IF NOT EXISTS ( SELECT Reg_Num FROM PIN WHERE Reg_Num=@Reg_Num and Program=@Program and P_Session=@Session) BEGIN UPDATE PIN SET Tries= Tries+1 WHERE Reg_Num=@Reg_Num --incrases the value of tries by 1 each time pin is used SELECT @Tries[Tries] end ELSE UPDATE PIN SET Reg_num = @Reg_Num P_Session=@Session Tries=Tries+1 Program=@Program Where Pin_no=@Pin_no --Updates database pin value for used pin on first usage end end If @Tries is not Null BEGIN if NOT EXISTS ( SELECT Pin_no FROM PIN WHERE Pin_no=@Pin_no) BEGIN DELETE FROM [PIN ] WHERE (( [Tries] = 3)) --Check the Amount of times pin has been used if >3 delete entire pin row end end ELSE BEGIN SELECT -1 END

Answer

pin check/ verification on stored procedure

Umeh Chukwudi

348

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. <div> Tries; - these holds the amount of times PIN has been used and if more than 3 times PIN row deletes.</div><div>i have designed this code as below . pls help ;</div><div> </div><div> <div class="dp-highlighter"><ol class="dp-sql" start="1"><li class="alt">CREATE PROCEDURE [dbo].PinValidation  </li><li class="">    @Pin_no varchar(24),  </li><li class="alt">    @Reg_Num varchar(24),  </li><li class="">    @Session varchar(50),  </li><li class="alt">    @Tries INT,  </li><li class="">    @Program varchar(50)  </li><li class="alt">AS  </li><li class="">begin  </li><li class="alt">    SELECT Pin_no FROM PIN  </li><li class="">    WHERE Pin_no=@Pin_no  </li><li class="alt">    if @Reg_Num is not Null  </li><li class="">    BEGIN  </li><li class="alt">    IF NOT EXISTS (SELECT Reg_Num FROM PIN WHERE Reg_Num=@Reg_Num and Program=@Program and P_Session=@Session)  </li><li class="">    BEGIN  </li><li class="alt">    UPDATE PIN  </li><li class="">    SET Tries= Tries+1  </li><li class="alt">    WHERE Reg_Num=@Reg_Num--incrases the value of tries by 1 each time pin is used  </li><li class="">    SELECT @Tries[Tries]  </li><li class="alt">    end  </li><li class="">    ELSE  </li><li class="alt">    UPDATE PIN  </li><li class="">    SET Reg_num = @Reg_Num, P_Session=@Session, Tries=Tries+1, Program=@Program  </li><li class="alt">    Where Pin_no=@Pin_no --Updates database pin value for used pin on first usage  </li><li class="">     end  </li><li class="alt">     end  </li><li class="">     If @Tries is not Null   </li><li class="alt">     BEGIN  </li><li class="">    if NOT EXISTS (SELECT Pin_no FROM PIN WHERE Pin_no=@Pin_no)  </li><li class="alt">     BEGIN  </li><li class="">     DELETE FROM [PIN ]  </li><li class="alt">     WHERE  (( [Tries] = 3))--Check the Amount of times pin has been used if >3 delete entire pin row  </li><li class="">     end  </li><li class="alt">     end  </li><li class="">     ELSE  </li><li class="alt">     BEGIN  </li><li class="">     SELECT -1  </li><li class="alt">     END  </li></ol></div></div>