Inconsistency in User-defined function and procedure

Help Procedural SQL
1

Hi, I have a problem related to the inconsistency between user-defined function and procedure.

To be specific, I have a table of 1M records contain 16-digit number, which is the card number. I applied the Luhn algorithm to find the valid card numbers among them.

I implemented the algorithm in the term of user-defined function which receive a string - varchar(16) contains the card number and output varchar(7) =[Valid”, “Invalid”].

I then applied the function on the entire table, but encounter some problems:

  1. There are some records in the column valid_state are NULL, although the algorithm does not return NULL and the function has been applied on all the records.

  2. Some card numbers are invalid(I checked it with other program) but are marked as valid. I even use SELECT check_luhn(number) to see the answer but it is still valid after many runs.

  3. When rewriting the whole function into procedure to test each number by hand and insert result into different table, the result is correct(valid number marked as valid, invalid marked as invalid)

Above are some problems I encountered when using the 8-hour trial since I now can’t afford to run on premises.

I will be highly appreciate to receive any help or guide or tips to fix the issue.

Thanks in advance.

Below are the screenshot of the user-defined function and procedure as well as the sample data.

function
function1276×1660 234 KB
procedure
procedure2500×1492 325 KB