 ,

If you ever need to create a credit card like number this stored procedure is for you.

Most credit cards contain a check digit, which is the digit at the end of the credit card number.

To generate the check digit, the LUHN formula is applied to the number. To validate the credit-card number, the check digit is figured into the formula.

Here's how the algorithm works for verifying credit cards; the math is quite simple:

1) Starting with the second to last digit and moving left, double the value of all the alternating digits.

2) Starting from the left, take all the unaffected digits and add them to the results of all the individual digits from step 1. If the results from any of the numbers from step 1 are double digits, make sure to add the two numbers first (i.e. 18 would yield 1+8). Basically, your equation will look like a regular addition problem that adds every single digit.

3) The total from step 2 must end in zero for the credit-card number to be valid.

http://www.webopedia.com/TERM/L/Luhn_formula.html
http://www.beachnet.com/~hstiles/cardtype.html
http://euro.ecom.cmu.edu/resources/elibrary/everycc.htm

```create procedure sp_AddCheckDigid
@num varchar(16) output
as

if Len(Ltrim(Rtrim(@num))) <> 15 RETURN -1

declare
@num_temp_char varchar(2),
@num_temp_int int,
@num_total int,
@index int
select @index = 1, @num_total = 0

While @index <= Len(@num)
Begin
Set @num_temp_char = SUBSTRING ( @num , @index , 1 )
If (@index % 2) <> 0
Begin
set @num_temp_int = Cast(@num_temp_char as int)
set @num_temp_int = @num_temp_int * 2
set @num_temp_char = Cast(@num_temp_int as varchar(2))
if Len(@num_temp_char) = 2
set @num_total = @num_total + (Cast(Left(@num_temp_char,1) as int) + Cast(Right(@num_temp_char,1) as int))
else
set @num_total = @num_total + Cast(@num_temp_char as int)
End
Else
Begin
set @num_total = @num_total + Cast(@num_temp_char as int)
End
set @index = @index + 1
End

Set @num_temp_char = Cast(10 - Cast(Right(Cast(@num_total as varchar(2)),1) as int) as varchar(2))
Set @num = @num + @num_temp_char

GO```