My stored procedure is
x
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
Advertisement
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'