Deliverable VI – Stored Programs

sProc #1:

  • modify the Product_Category (or Model) table to include a new Boolean attribute called reorderNecessary that will be set to true when an item needs to be reordered. The attribute should default to false. [do this prior to writing stored procedure]
  • Since the default value may not be valid for all the data in your table, write a stored procedure to set the value of the new reorderNecessary attribute to its correct value.
    • If the modelInventoryCount is less than or equal to the modelReorderQuantity, then set the reorderNecessary value to true.
    • Otherwise set the reorderNecessary value to false.
    • Execute the procedure to reset the value.

sProc #2:

  • The next stored program is more complex. When an item is purchased by credit card, the card number must be validated. The algorithm to accomplish this is fairly straightforward, and consists of three steps. These steps are performed by working from the rightmost digit of the credit card number.
    • Step 1: Double the value of alternate digits of the primary account number beginning with the second digit from the right (the right-most digit is the check digit.)
    • Step 2: Still working from the right, add the individual digits comprising the products obtained in Step 1 to each of the unaffected digits in the original number. For example, if the product is 12 then add 1 + 2 to the unaffected digit to the right.
    • Step 3: The total obtained in Step 2 must be a number ending in zero (or mod 10 = 0) for the account number to be validated.
      70 mod 10 = 0 ∴ Card number is valid
  • Modify the following SQL Server sProc to validate a credit card. You can use a stored procedure or stored function.

    CREATE PROCEDURE [dbo].[prValidateCreditCard] (@creditCardNum char(16)) AS

    BEGIN
    DECLARE @counter INT, @sum INT, @number INT, @tmp INT, @result BIT
    SET @result=0
    IF @creditCardNum IS NULL
      GOTO lblFail
    IF len(@creditCardNum)=0
      GOTO lblFail
    SET @counter=1
    WHILE @counter<=len(@creditCardNum)
      BEGIN
        IF ISNUMERIC(substring(@creditCardNum,@counter,1))=0
          GOTO lblFail
        SET @counter=@counter+1
      END
    SET @sum=0
    SET @number=0
    SET @counter=len(@creditCardNum)
    WHILE @counter>0
      BEGIN
        IF @counter>1
          BEGIN
            SET @tmp=(ASCII(substring(@creditCardNum,@counter-1,1))-48)*2
            IF @tmp>9
              SET @sum=@sum+@tmp-9
            ELSE
              SET @sum=@sum+@tmp
          END
          SET @number=@number+(ASCII(substring(@creditCardNum,@counter,1))-48)
          SET @counter=@counter-2
      END
    SET @sum=(@sum+@number) % 10
    IF @sum=0
      BEGIN
        print 'good'
        SET @result=1
      END
    lblFail:
      RETURN (@result)
    END
    GO

  • Test it with a valid credit card number like 5268080043376894.