CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ninja Coder
Ninja Coder

Reputation: 29

Error converting data type varchar to int error in stored procedure

My stored procedure is

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Customer_Registrations]
    @type varchar(50) = null,
    @Customer_ID int = null,
    @Customer_Name varchar(50) = null,
    @Email varchar(50) = null,
    @Password varchar(50) = null,
    @Mobile varchar(50) = null
AS
BEGIN
    SET NOCOUNT ON;

    IF @type = 'select'
    BEGIN
        SELECT * 
        FROM ssshub..Customer_Master
    END 

    IF @type = 'specific'
    BEGIN
        SELECT * 
        FROM ssshub..Customer_Master 
        WHERE Customer_ID = @Customer_ID
    END

    IF @type = 'insert'
    BEGIN
        INSERT INTO [dbo].[Customer_Master]([Customer_Name], [Email],[Password], [Mobile], [SDate])
        VALUES ('@Customer_Name', '@Email', '@Password', '@Mobile', CURRENT_TIMESTAMP)
    END
END

And my table

This is working fine

This query works fine but when I am trying to insert it using stored procedure it throws an error.

I have an identity specification for customer_id on so no need to insert it

INSERT INTO [dbo].[Customer_Master] ([Customer_Name], [Email], [Password], [Mobile], [SDate])
VALUES('ewcewc', '[email protected]', 'dewdwd', '9999999999', CURRENT_TIMESTAMP)

Now when I am trying to execute my stored procedure with the following statement

 exec customer_registrations 'insert', 'dsad', '[email protected]', 'pass', '9999900000'

I get:

Error converting data type varchar to int

Upvotes: 1

Views: 23567

Answers (3)

Sunil Das
Sunil Das

Reputation: 1

Create Database Test

Create Table Employee
(
EmpId int identity(1,1) not null primary Key,
EmpName varchar(50),
Address varchar(50),
MobileNo int
)

Select * From [dbo].[Employee]

Alter Table [dbo].[Employee] Alter Column MobileNo Bigint

Insert into [dbo].[Employee] values('Harsh Kumar','Banglore','9748342075')

Create Proc MyProc
As
Select * From [dbo].[Employee]

EXEC MyProc

Create Proc SP_GetEmp
@EmpId int,
@EmpName varchar(50),
@Address varchar(50),
@MobileNo int
As
Insert into [dbo].[Employee](EmpId, EmpName, Address, MobileNo) Values(@EmpId, @EmpName, @Address, @MobileNo)

EXEC SP_GetEmp 'Rahul Kr','Pune', 8250707544

EXEC MyProc

Error:- Error converting data type varchar to int.

Upvotes: 0

Dhaval Asodariya
Dhaval Asodariya

Reputation: 558

Please pass null value in procedure while you insert the data in table. Run the procedure below way while inserting data in table. you should me manage all the parameter of procedure. you are not passed value of Customer_ID, so that's way error occur.

EXEC [dbo].[Customer_Registrations] 'insert',null,'ewcewc', '[email protected]', 'dewdwd', '9999999999'

Upvotes: 0

Jacob
Jacob

Reputation: 78850

Lining up your arguments with the parameters:

'insert'          -> @type varchar(50)
'dsad'            -> @Customer_ID int    
'[email protected]'   -> @Customer_Name varchar(50)
'pass'            -> @Email varchar(50)
'9999900000'      -> @Password varchar(50)
                  -> @Mobile varchar(50) = null

...you can see that 'dsad' has no way of being understood as an int. Maybe there's a problem elsewhere as well, but at the very least, the stored procedure is not being called correctly.

Update:

If your intent was to omit some arguments that aren't relevant in a certain case, you have to use named parameters; otherwise, arguments can only be applied in the same order the parameters appear:

exec customer_registrations 
  'insert', 
  @Customer_Name = 'dsad', 
  @Email         = '[email protected]', 
  @Password      = 'pass', 
  @Mobile        = '9999900000';

Upvotes: 3

Related Questions