.Fill Overflow on Phone Number string Auto-converting to Int-CodePudding

I'm checking a user-uploaded list of phone numbers against a proper phone number format and a database table of "Do Not Call" phone numbers and returning the list entries that aren't formatted correctly or don't exist in the table. The phone numbers are all defined as varchar, but the .Fill is automatically converting it to int and throwing an overflow error. Does anyone know how to get the .Fill to keep the phone numbers as varchar? I've tried declaring the filled table columns beforehand and defining the phone number outputs as bigint in the stored procedure, but those haven't worked. Every other piece of code in the application works fine. Thanks in advance!

Error Message, where 'x' denotes a phone number: System.Data.SqlClient.SqlException (0x80131904): The conversion of varchar value 'xxxxxxxxxx' overflowed an int column. The statement has been terminated.

C#:

System.Data.DataTable cleanedList = new System.Data.DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("dbo.uspCleanScrubberLeads", conn))
using (var da = new SqlDataAdapter(cmd))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ProcedureDate", DateTime.Now);
    cmd.Parameters.AddWithValue("@LeadListTable", dt);
    da.Fill(cleanedList);//Error occurs here!!!
}

SQL:

ALTER PROCEDURE [dbo].[uspCleanScrubberLeads]
    @ProcedureDate datetime2(0),
    @LeadListTable AS LeadListTable READONLY
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #CleanedLeads (PhoneNumber varchar(20), Reason varchar(200))
    INSERT INTO #CleanedLeads (PhoneNumber, Reason)
    SELECT PhoneNumber, '' FROM @LeadListTable AS t1
    UPDATE #CleanedLeads
        SET Reason = 'Non-numeric phone number' WHERE ISNUMERIC(PhoneNumber) <> 1
    UPDATE #CleanedLeads
        SET Reason = 'Phone is greater than 10 digits' WHERE Reason = '' AND LEN(PhoneNumber) > 10
    UPDATE #CleanedLeads
        SET Reason = 'Phone is fewer than 10 digits' WHERE Reason = '' AND LEN(PhoneNumber) < 10

    --Remove numbers in the DoNotCall table
    DELETE FROM #CleanedLeads WHERE EXISTS (SELECT * FROM DoNotCall WHERE ISNUMERIC(DNCTelephone) = PhoneNumber) 
        AND Reason = ''

    SELECT * FROM #CleanedLeads
END

CodePudding user response：

In your statement:

DELETE FROM
    #CleanedLeads
WHERE
    EXISTS (
        SELECT
            *
        FROM
            DoNotCall
        WHERE
            ISNUMERIC(DNCTelephone) = PhoneNumber
            AND Reason = ''
    )

Right here, you're comparing ISNUMERIC(DNCTelephone) to the VARCHAR(20) field PhoneNumber.