Error converting data type varchar to int.?

Question

My table is below

CREATE TABLE Customers
(
     CustomerID int identity(1,1) not null primary key,
     Name varchar(50) not null,
     PhoneNumber varchar(20) not null 
         constraint chk_PhoneNumber check(PhoneNumber not like '%[^0-9]%'),
     DoorNo varchar(50) not null,
     StreetName varchar(50) not null,
     City varchar(50) not null,
     Statee varchar(50) not null,
     Zipcode int not null
)

My stored procedure:

ALTER PROCEDURE stp_customers_insert
    (@customerid int, 
     @name varchar(50),
     @phone varchar(50),
     @doorno varchar(50),
     @streetname varchar(50),
     @city varchar(50),
     @state varchar(50),
     @zip int)
AS
BEGIN
    IF EXISTS (SELECT CustomerID FROM Customers WHERE CustomerID = @customerid)
    BEGIN
        RAISERROR ('employee id already exists', 1, 1)
    END
    ELSE
    BEGIN
        INSERT INTO Customers (Name, PhoneNumber, DoorNo, StreetName, City, Statee, Zipcode) 
        VALUES (@name, @phone, @doorno, @streetname, @city, @state, @zip)
    END
END

Sample call:

exec stp_customers_insert 'ram', '674673932', '122', '5th cross', 'trichy', 'tamilnadu', 620001

I get this error:

Msg 8114, Level 16, State 5, Procedure stp_customers_insert, Line 23
Error converting data type varchar to int.

Answer 1

The problem appears to be that your stored procedure expects 8 parameters:

stp_customers_insert(@customerid int, @name varchar(50), @phone varchar(50),
                     @doorno varchar(50), @streetname varchar(50), @city varchar(50),
                     @state varchar(50), @zip int)

but you are only passing 7 parameters when you actually call the proc:

exec stp_customers_insert 'ram','674673932','122','5th cross','trichy','tamilnadu',620001

If you don't know or don't want to perform the duplicate check on the CustomerID, then you could slightly modify your call to just pass NULL:

exec stp_customers_insert NULL, 'ram','674673932','122','5th cross','trichy','tamilnadu',620001

As an aside, if the proc is not even inserting the CustomerID, and this field is auto increment, then I don't see the point of passing it. Instead, you might want to consider using a unique constraint to achieve the same.

Answer 2

exec stp_customers_insert 1,'ram','674673932','122','5thcross','trichy','tamilnadu',620001

You have to pass @customerid value in procedure parameters - then it will execute without error.

Answer 3

In your table structure CustomerID is defined as INT. But as your stored procedure is defined in this format:

stp_customers_insert(@customerid int,@name ....

You are sending ram as value for customerid in

exec stp_customers_insert 'ram','674673932'....

Correct this to:

exec stp_customers_insert '*enter the CustId value here*','ram','674673932'....

Replace *enter the CustId value here* with the CustomerID

Also change:

insert into Customers(Name,PhoneNumber,DoorNo,StreetName,City,Statee,Zipcode) values(@name,@phone,@doorno,@streetname,@city,@state,@zip)

To include CustomerID as:

insert into Customers(CustomerID,Name,PhoneNumber,DoorNo,StreetName,City,Statee,Zipcode) values(@customerid,@name,@phone,@doorno,@streetname,@city,@state,@zip)

Answer 4

I suggest remove @customerid from sp CustomerID is auto increment field so no need to pass any value for CustomerID. It should be like this:

alter procedure stp_customers_insert(@name varchar(50),@phone varchar(50),@doorno varchar(50),@streetname varchar(50),@city varchar(50),@state varchar(50),@zip int)
as
    begin
    if exists(select CustomerID from Customers where Name = @name ,phone = @phone ,doorno = @doorno ,streetname = @streetname ,city= @city,state= @state , zip = @zip )
    begin
        raiserror('employee id already exists',1,1)
    end
else
    begin
        insert into Customers(Name,PhoneNumber,DoorNo,StreetName,City,Statee,Zipcode) values(@name,@phone,@doorno,@streetname,@city,@state,@zip)
    end
end

exec stp_customers_insert 'ram','674673932','122','5th cross','trichy','tamilnadu',620001