pin check/ verification on stored procedure

Jun 2 2016 11:47 AM

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>

Ambiguous column name 'Associate_ID'.

error while trying to connect the sql server.

Umeh Chukwudi

pin check/ verification on stored procedure

Answers (0)