Dhaval Bulsara Dhaval Bulsara - 3 months ago 9
SQL Question

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', 'dewdw@dwc.com', 'dewdwd', '9999999999', CURRENT_TIMESTAMP)


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

exec customer_registrations 'insert', 'dsad', 'test@test.com', 'pass', '9999900000'


I get:


Error converting data type varchar to int

Answer

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', 'dewdw@dwc.com', 'dewdwd', '9999999999'
Comments