为什么我在SP中将varchar转换为数字时出错（sql server 2005） [英] Why I am getting an error converting varchar to numeric in SP (sql server 2005)
本文介绍了为什么我在SP中将varchar转换为数字时出错（sql server 2005）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
为什么我收到将varchar转换为数字的错误？ Sp执行得很好。但是在exec sp上显示的值是显示的mentiioned错误。
Why I am getting an error converting varchar to numeric? Sp executes fine. But on exec sp with values the mentiioned error shown.
/****** Object: StoredProcedure [dbo].[FETCH_SALARY_UPLOADED_DETAILS] Script Date: 03/12/2014 18:16:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
Module : C3PSPS
Author : Alexander
Date : 12/03/2014
Name : FETCH_SALARY_UPLOADED_DETAILS
Description : FETCH THE SALARY UPLOADED DETAILS
Parameters :
Tables : Employee,Corporate,EmpSalary,CorproateFileUploadQueue
Associated PL/SQLs :
Warnings :
See Also :
exec [FETCH_SALARY_UPLOADED_DETAILS] 3,2014,'IBM',1
exec [FETCH_SALARY_UPLOADED_DETAILS] 5,2013,'0',0
************************************************************/
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
--Setting the To month and To Year
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
--To check and Fetch Salary Processed under Salary Uploaded Details Fetching(Start Month)
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
--To check and Fetch Salary Non Processed Report under Salary Uploaded Details Fetching(Active Card at the end of month - Salary Month)
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
--Fetching Employee Deatils having active card
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
--Excluding Salary Processed Details
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)
--GROUP BY E.c3empregid)
end
END
推荐答案
将SP分成几部分。在SQL Server Management Studio中运行它们，并尝试找出哪些区域引发错误。
或者，您可以尝试SQL SP调试 - 如何：调试存储过程 [ ^ ]
Break the SP into parts. Run each of them in SQL Server Management Studio and try to figure out which of these areas is throwing an error.
Alternatively, you can try SQL SP debugging - How to : Debug Stored Procedures[^]
你发布了期待任何人阅读所有内容的代码。哪些行给出错误？该错误几乎肯定与传入的值不是实际数字有关。 ISNUMERIC [ ^ ]自2005年以来一直受支持，所以你应该编写使用它的代码来决定何时尝试将值更改为一个数字（例如，在您的语句中添加AND ISNUMERIC（myCol），以便过滤掉非数字）
You posted too much code to expect anyone to read it all. What lines give the error ? The error is almost certainly to do with the values being passed in that are not actual numbers. ISNUMERIC[^] has been supported since 2005, so you should write code that uses that to decide when to try to change a value in to a number ( for example, add AND ISNUMERIC(myCol) to your statement so it filters out non numbers )
这篇关于为什么我在SP中将varchar转换为数字时出错（sql server 2005）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
