Conversion failed when converting the varchar value to data type int.

Posted by Parthibansk under Sql Server on 12/14/2012 | Points: 10 | Views : 5153 | Status : [Member] | Replies : 1
I get this error when i exec the storedproc:

"Conversion failed when converting the varchar value ' Already Exists' to data type int."

CREATE proc [dbo].[chkroll]
@roll int,@name varchar(50),@address varchar(50),@result char(10), @error varchar(100) out

as

begin

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message
if not exists(select * from students where roll= @roll)

begin

insert into students
(roll,name,address,result) values (@roll,@name,@address,@result)

--If User Successfully Registerd I am returing this Message as Output Parameter


SET @ERROR=@roll+' Registered Successfully'
END
ELSE
BEGIN
--If User already Exists i am returning this Message as Output Parameter
SET @ERROR=@roll + ' Already Exists'
END
END

GO




Responses

Posted by: Pandians on: 12/14/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check it out!

Root cause of the issue is SET @ERROR = @roll + ' Registered Successfully'
CREATE PROC [dbo].[chkroll]

(
@roll INT,
@name VARCHAR(50),
@address VARCHAR(50),
@result CHAR(10),
@error VARCHAR(100) OUT
)
AS
BEGIN
SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message
IF NOT EXISTS(SELECT 1 FROM students WHERE roll= @roll)
BEGIN
INSERT INTO students(roll,name,[address],result)
values (@roll,@name,@address,@result)

--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR = CAST(@roll AS VARCHAR) + ' Registered Successfully'
END
ELSE
BEGIN
--If User already Exists i am returning this Message as Output Parameter
SET @ERROR = CAST(@roll AS VARCHAR) + ' Already Exists'
END
END
GO


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response