Microsoft SQL Server - check constraint error when using stored procedure (msg 547)

Question

I keep getting the following error:

Msg 547, Level 16, State 0, Procedure add_new_customer, Line 6
The INSERT statement conflicted with the CHECK constraint "CK__customer__addres__09A971A2". The conflict occurred in database "AIT 732 - Bank", table "dbo.customer", column 'address_state'.

The following is table that I am attempting to update:

create table customer
(
cust_id numeric(10,0) not null identity primary key,
social_security_num numeric(9,0) not null,
first_name varchar(20) not null,
last_name varchar(20) not null,
primary_address varchar(50) not null,
address_zip_code numeric(5,0) not null,
address_state char(2) check (address_state in('md','pa','dc', 'de', 'wv', 'va','nj')),
gender char(1) not null check (gender in('m','f')),
phone_num numeric(10,0) null
)

this is the stored procedure i created:

create procedure add_new_customer
    (@social_sec numeric, @f_name varchar, @l_name varchar, @pri_address varchar, @zip numeric, @add_state char, @gender char, @phone numeric)
    as
    begin
        begin transaction
        insert into customer(
        social_security_num,
        first_name,
        last_name,
        primary_address,
        address_zip_code,
        address_state,
        gender,
        phone_num)
        values (
        @social_sec, 
        lower(@f_name), 
        lower(@l_name),
        lower(@pri_address), 
        @zip, 
        lower(@add_state), 
        lower(@gender), 
        @phone)
    if @@ERROR != 0 
    begin
        rollback transaction
        return @@error
    end
    commit transaction
 end

and last but not least, this is how I am calling procedure

add_new_customer 211118888, 'Bob', 'JONES', '222 some st', 21333, 'md', 'm', 4102227878

I've been messing with this for about and hour and can not figure out where that error is coming from. any help would be appreciated.

Answer 1

Your stored proc parameter definition is @add_state char but this will truncate your input value of 'md' to 'm' which isn't a valid value. Change you parameter definition to match the table defn char(2) and it should then work.