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
Replies
Know the answer? Post it — somebody with the same question will find it here.