Why I am getting an error converting varchar to numeric? Sp executes fine. But on exec sp with values the mentiioned error shown.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FETCH_SALARY_UPLOADED_DETAILS]
(
@p_Month INT,
@p_Year INT,
@p_corporateid VARCHAR(10),
@p_reportTypeId INT
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @p_error VARCHAR(40)
DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
DECLARE @loc_ToMonth INT
DECLARE @loc_ToYear INT
DECLARE @To_Day DATETIME
DECLARE @FromDateString VARCHAR(12)
DECLARE @ToDateString VARCHAR(12)
SET @FromDateString = CAST((@P_Month) AS VARCHAR)+'/01/'+CAST((@p_Year) AS VARCHAR)
SET @FromDate = CAST(@FromDateString AS DATETIME)
print @FromDate
IF(@P_Month + 1 = 13)
BEGIN
SET @loc_ToMonth = 1
SET @loc_ToYear = @p_Year + 1
SET @ToDateString = CAST(@loc_ToMonth AS VARCHAR)+'/01/'+CAST (@loc_ToYear AS VARCHAR)
print @ToDateString
END
ELSE
BEGIN
SET @loc_ToMonth = @P_Month
SET @loc_ToYear = @p_Year
SET @ToDateString = CAST(@loc_ToMonth +1 AS VARCHAR)+'/01/'+CAST (@loc_ToYear AS VARCHAR)
END
SET @ToDate = CAST((@ToDateString) AS DATETIME)
SET @ToDate = @ToDate
print @ToDate
if( @p_reportTypeId = 0)
begin
SELECT distinct E.EmpId ,E.CorporateId ,
isnull(E.FirstName,'')+' '+isnull(E.LastName,'') AS NAME, E.AccountNo,
[dbo].[fnMaskCardNo](E.CardNo) as CardNo,
ES.Salary AS Amount,
CASE WHEN E.FollowMOLWorkFlow=1 THEN 'YES'
ELSE 'NO' END as WPS,convert(VARCHAR,CUFQ.UploadedDate,103) as Date
FROM dbo.Employee E INNER JOIN
dbo.Corporate C ON C.CorporateId = E.CorporateId
INNER JOIN
dbo.EmpSalary ES ON ES.CorporateId = E.CorporateId
AND ES.EmpId=E.EmpId
INNER JOIN
dbo.CorporateFileUploadQueue CUFQ ON CUFQ.CorporateId= C.CorporateId
WHERE (E.CorporateId=@p_corporateid OR @p_corporateid='0') and
ES.SALMONTH=@p_Month and ES.SALYEAR=@p_Year
AND ES.Mode in('U')
and CUFQ.UploadedDate is not null
and C.IsDeleted=0
GROUP BY E.EmpId ,E.CorporateId,
isnull(E.FirstName,'')+' '+isnull(E.LastName,''),E.AccountNo, [dbo].[fnMaskCardNo](E.CardNo),E.FollowMOLWorkFlow,CUFQ.UploadedDate,ES.Salary
end
else if( @p_reportTypeId = 1)
begin
SELECT distinct E.EmpId,E.CorporateId,
isnull(E.FirstName,'')+' '+isnull(E.LastName,'') AS NAME, E.AccountNo,
[dbo].[fnMaskCardNo](E.CardNo) as CardNo,
'0.00' AS Amount,
CASE WHEN E.FollowMOLWorkFlow=1 THEN 'YES'
ELSE 'NO' END as WPS,convert(VARCHAR,CUFQ.UploadedDate,103) as Date
FROM dbo.Employee E INNER JOIN
dbo.Corporate C ON C.CorporateId = E.CorporateId
INNER JOIN
dbo.CorporateFileUploadQueue CUFQ ON CUFQ.CorporateId= C.CorporateId
WHERE
c3empregid not in(
select c3empregid from employee where
Isinactivatedfordeletion=1 AND
EODblockedStatus = 1 AND Frozen = 'Y'
and Actual_InactivationDate < @ToDate
AND (CorporateId= @p_corporateId or @p_corporateId='0'))
AND (E.CardNo IS NOT NULL) AND E.CardStatus='L' AND (E.CorporateId= @p_corporateId or @p_corporateId=0) AND E.IsDeleted=0
AND E.RHFReceivedDate <= @ToDate
AND E.c3empregid not in(
SELECT E.c3empregid
FROM dbo.Employee E INNER JOIN
dbo.Corporate C ON C.CorporateId = E.CorporateId INNER JOIN
dbo.EmpSalary ES ON ES.CorporateId = E.CorporateId
AND ES.EmpId=E.EmpId
INNER JOIN
dbo.CorporateFileUploadQueue CUFQ ON CUFQ.CorporateId= C.CorporateId
WHERE (E.CorporateId=@p_corporateid OR @p_corporateid='0') and
ES.SALMONTH=@p_Month and ES.SALYEAR=@p_Year
AND ES.Mode in('U')
and CUFQ.UploadedDate is not null
and C.IsDeleted=0)
end
END