Hi Everyone

I m trying to convert the data of one table into two tables i.e. if the data is valid it needs to be dumped into VALID table else into ERROR table but i m encountering following error while casting during select "Syntax error converting the varchar value '$ED(RSNYRC_M,' to a column of data type int."

The reason is that the tblEmpsxDailyExc_HIST table cotains lot of invalid values .

So please can someone help me out how to handle this problem that at the time of select itself if the value is not of proper data format i can dump that row into ERRROR table.

CREATE PROCEDURE uspTransferDATAEXC
AS

DECLARE @Date datetime
DECLARE @Office varchar(50)
DECLARE @AgentPin char(4)
DECLARE @AgentSSN int
DECLARE @AgentName varchar(50)
DECLARE @CodeType varchar(50)
DECLARE @Code varchar(50)
DECLARE @VarDuration varchar(50)
DECLARE @Duration int
DECLARE @CreatedDate datetime


DECLARE CursorEXC CURSOR FOR

SELECT cast([Date] as datetime),
    Office,
    cast(AgentPin as char(4)),
    cast(AgentSSN as int),
    AgentName,
    CodeType,Code,VarDuration,
    cast(Duration as int),
    CreatedDate

FROM tblEmpsxDailyExc_HIST



OPEN CursorEXC

FETCH NEXT FROM CursorEXC INTO

@Date,@office,@AgentPin,@AgentSSN,@AgentName,
@CodeType, @Code, @VarDuration, @Duration, @CreatedDate

WHILE(@@FETCH_STATUS = 0)

BEGIN

IF
    (isdate(@Date)=1) AND
    @Office IS NOT NULL AND
    @AgentPin IS NOT NULL AND
    (isnumeric(@AgentSSN)=1) AND
    @AgentName IS NOT NULL AND
    @CodeType is NOT NULL AND
    @Code is NOT NULL AND
    @VarDuration IS NOT NULL AND
    (isnumeric(@Duration)=1) AND
    (isdate(@CreatedDate)=1)

INSERT INTO tblEmpsxDailyExc_VALID([Date],Office,AgentPin,
AgentSSN,AgentName,CodeType,Code, VarDuration, Duration,CreatedDate)

VALUES(@Date,@office,@AgentPin,@AgentSSN,@AgentNam e,
@CodeType, @Code, @VarDuration, @Duration, @CreatedDate)

ELSE

INSERT INTO tblEmpsxDailyExc_ERROR([Date],Office,AgentPin,
AgentSSN,AgentName,CodeType,Code, VarDuration, Duration,CreatedDate)

VALUES(@Date,@office,@AgentPin,@AgentSSN,@AgentNam e,
@CodeType, @Code, @VarDuration, @Duration, @CreatedDate)

FETCH NEXT FROM CursorEXC INTO

@Date,@office,@AgentPin,@AgentSSN,@AgentName,
@CodeType, @Code, @VarDuration, @Duration, @CreatedDate


END

CLOSE CursorEXC
DEALLOCATE CursorEXC
GO