Aleena Saviour

Aleena Saviour

  • NA
  • 519
  • 88.3k

Conversion error in stored procedure

Nov 21 2016 6:04 AM
"Conversion failed when converting the varchar value 'WHERE FileId=' to data type int."
help me with the reason for above error in stored procedure.
 
ALTER PROCEDURE [dbo].[HR_spEmpFiles_Save]
@FileId int,
@EmpId int,
@Passport varchar(500)= null,
@Visa varchar(500)=null,
@EmiratesId varchar(500)=null,
@LabourCard varchar(500)=null,
@Photo varchar(500)=null,
@Signature varchar(500)=null,
@Status int
AS
BEGIN
SET NOCOUNT ON
declare @set varchar(200),@query varchar(500),@where varchar(100)
SET @set=' ';
SET @where='WHERE ';
IF @FileID!=0
BEGIN
if(@Passport IS NOT NULL)
BEGIN
SET @set=@set+' Passport='+@Passport
END
if(@Visa IS NOT NULL)
BEGIN
SET @set=@set+' Visa='''+@Visa+'''';
END
if(@EmiratesId IS NOT NULL)
BEGIN
SET @set=@set+' EmiratesId='+@EmiratesId
END
if(@LabourCard IS NOT NULL)
BEGIN
SET @set=@set+' LabourCard='+@LabourCard
END
IF (@Photo IS NOT NULL)
BEGIN
SET @set=@set+' Photo='+@Photo;
END
IF(@Signature IS NOT NULL)
BEGIN
SET @set=@set+' Signature='+@Signature
END
SET @query='UPDATE tblEmpFiles SET '
SET @where=@where+' FileId='+@FileId;
SET @where=@where+' AND Status=1';
SET @query=@query+@set+@where;
PRINT(@query)
EXEC(@query);
END
ELSE
BEGIN
INSERT INTO tblEmpFiles(EmpId,Passport,Visa,EmiratesId,LabourCard,Photo,Signature,Status) VALUES(@EmpId,@Passport,@Visa,@EmiratesId,@LabourCard,@Photo,@Signature,@Status)
END
--SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
END

Answers (5)