Conversion failed when converting the varchar value '****' to data type int

0
votes

I am using Stored Procedure in Sql Server 2005.

I am getting this error:

"Conversion failed when converting the varchar value 'My_41.png' to data type int."

imageID is declared as varchar(300) in tbl_itemImage @ReturnValue is declared as varchar(MAX)

Insert and Update Query executed well, and Data is also Stored in Table. But at the end when SELECT Query is executed it gives me an above error.

This is my STORED PROCEDURE

insert into 
tbl_itemImage(itemID,imageID,name,isCoverImage,createdBy,createdOn)
values      
(@itemID,@imageID,@name,@isCoverImage,@ID,getdate())  



update tbl_itemImage
set imageID =  @imageID + CAST(@@Identity as varchar(10))+ @imageExtension
where   itemimageID = @@Identity  AND imageID <> '' 


DECLARE @ReturnValue  varchar(MAX)

select @ReturnValue = imageID from tbl_itemImage where itemImageID  = @@Identity 

Return  @ReturnValue
1
sql-serverintvarchar
The value returned by RETURN statement(msdn.microsoft.com/en-us/library/ms174998.aspx) should be INT not VARCHAR(MAX). Instead, please use OUTPUT parameters (msdn.microsoft.com/en-us/library/ms187926.aspx). Anyway, RETURN IntValue should be used to return an error code. - Bogdan Sahlean
Do not use @@identity ever - use scope_identity() instead or the output clause. If you ever get trigger on this table that inserts to another table with an identity, @@identity will return the wrong value which will mess up your data integrity beyond repair. - HLGEM

1 Answers

2
votes

The value returned by RETURN statement(msdn.microsoft.com/en-us/library/ms174998.aspx) should be INT not VARCHAR(MAX). Instead, please use OUTPUT parameters (msdn.microsoft.com/en-us/library/ms187926.aspx). Anyway, RETURN IntValue should be used to return an error code

by Bogdan Sahlean

Should be as an answer.