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:
There are some records in the column
valid_stateare NULL, although the algorithm does not return NULL and the function has been applied on all the records.
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.
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.