首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >为什么会发生将数据类型varchar转换为bigint的错误?

为什么会发生将数据类型varchar转换为bigint的错误?
EN

Stack Overflow用户
提问于 2016-08-17 08:58:17
回答 3查看 8.2K关注 0票数 0

我为什么要面对这个错误?这是非常恼人的,并试图解决它,但不起作用。

将数据类型varchar转换为bigint时出错

它引发有关此值2000100020502.的错误。

代码语言:javascript
运行
复制
ALTER PROCEDURE [dbo].[Reports_UnDisbursedSalaryReport]
(
    @FromDate SMALLDATETIME,
    @ToDate SMALLDATETIME,
    @OfficeID INT = 3,
    @Type INT = 1,
    @EmployerUniqueID VARCHAR(15) = '',
    @EmployeeUniqueID VARCHAR(15) = ''
)
AS
BEGIN
    SET @EmployerUniqueID = 
        CASE 
           WHEN LEN(@EmployerUniqueID) <=0 
              THEN NULL 
              ELSE @EmployerUniqueID 
        END
    SET @EmployeeUniqueID = 
        CASE 
           WHEN LEN(@EmployeeUniqueID) <= 0
              THEN NULL 
              ELSE @EmployeeUniqueID 
        END

    DECLARE @Branches TABLE (BranchID INT)

    IF @Type = 1
    BEGIN
        INSERT INTO @Branches 
            SELECT BranchID 
            FROM vw_OrganizationTree 
            WHERE OrganizationID = @OfficeID
    END

    IF @Type = 2
    BEGIN
        INSERT INTO @Branches 
            SELECT BranchID 
            FROM vw_OrganizationTree 
            WHERE CompanyID = @OfficeID
    END

    IF @Type = 3
    BEGIN
        INSERT INTO @Branches 
            SELECT BranchID 
            FROM vw_OrganizationTree 
            WHERE BranchID = @OfficeID
    END

    SELECT 
        DATEADD(HOUR, 4, PF.EntryDateTime) 'EntryDateTime', 
        C.CompanyName, B.BranchName, 
        @FromDate as 'Fromdate', @ToDate as 'Todate', 
        E.EmployerName, RE.EmployeeName, RE.EmployeeUniqueID, 
        FE.IncomeFixedComponent, FE.IncomeVariableComponent,
        S.StatusDescription
    FROM
        File_EdrEntries FE 
    JOIN 
        RegisteredEmployees RE ON RE.EmployeeUniqueID = FE.EmployeeUniqueID
    JOIN 
        Employers E ON E.ID = RE.Employer_ID 
    JOIN 
        Branches B ON B.BranchID = E.Branch_ID 
    JOIN 
        companies C ON C.COMPANYID = B.COMPANY_ID
    JOIN 
        Statuses S ON S.StatusID = FE.Status_ID 
    JOIN 
        PAFFiles PF ON PF.ID = FE.PAFFile_ID
    WHERE 
        PF.EntryDateTime >= @FromDate 
        AND PF.EntryDateTime < DATEADD(DAY, 1, @ToDate) 
        AND Status_ID IN (1, 2) 
        AND E.Branch_id IN (SELECT BranchID From @Branches)
        AND CAST(E.EmployerID AS BIGINT) = ISNULL(CAST(@EmployerUniqueID AS BIGINT), CAST(E.EmployerID AS BIGINT))
        AND CAST(RE.EmployeeUniqueID AS BIGINT) = ISNULL(CAST(@EmployeeUniqueID AS BIGINT), CAST(RE.EmployeeUniqueID AS BIGINT))
END
EN

回答 3

Stack Overflow用户

发布于 2016-08-17 09:10:44

您可以使用try_convert函数。我假设您使用的是sql server 2012及以上.

代码语言:javascript
运行
复制
SELECT DATEADD(HOUR, 4, PF.EntryDateTime) 'EntryDateTime'
    ,C.CompanyName
    ,B.BranchName
    ,@FromDate AS 'Fromdate'
    ,@ToDate AS 'Todate'
    ,E.EmployerName
    ,RE.EmployeeName
    ,RE.EmployeeUniqueID
    ,FE.IncomeFixedComponent
    ,FE.IncomeVariableComponent
    ,S.StatusDescription
FROM File_EdrEntries FE
JOIN RegisteredEmployees RE ON RE.EmployeeUniqueID = FE.EmployeeUniqueID
JOIN Employers E ON E.ID = RE.Employer_ID
JOIN Branches B ON B.BranchID = E.Branch_ID
JOIN companies C ON C.COMPANYID = B.COMPANY_ID
JOIN Statuses S ON S.StatusID = FE.Status_ID
JOIN PAFFiles PF ON PF.ID = FE.PAFFile_ID
WHERE PF.EntryDateTime >= @FromDate
    AND PF.EntryDateTime < DATEADD(DAY, 1, @ToDate)
    AND Status_ID IN (
        1
        ,2
        )
    AND E.Branch_id IN (
        SELECT BranchID
        FROM @Branches
        )
    AND try_convert(BIGINT, E.EmployerID) = ISNULL(try_convert(BIGINT, @EmployerUniqueID), try_convert(BIGINT, E.EmployerID))
    AND try_convert(BIGINT, RE.EmployeeUniqueID) = ISNULL(try_convert(BIGINT, @EmployeeUniqueID), try_convert(BIGINT, RE.EmployeeUniqueID))

如果try_convert函数在您的情况下工作,那么您的数据包含字母数字字符。

票数 2
EN

Stack Overflow用户

发布于 2016-08-17 09:04:09

可能是在转换列时,而不是变量:CAST(E.EmployerID AS BIGINT)CAST(RE.EmployeeUniqueID AS BIGINT)。您确定所有的Employers.EmployerIDRegisteredEmployees.EmployeeUniqueID都可以转换为bigint吗?

票数 0
EN

Stack Overflow用户

发布于 2016-08-17 11:32:38

select convert( bigint , 2000100020502)

这里的Server 2012很好

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38992195

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档