Sql存储过程中的错误

问题描述

INSERT INTO Tbl_Basicinfo(Employer_Id,Lname,Fname,UserName,Userpass,Location_Id,cc2,CC3,cc4,cc5,Dohire,Job_Status,ssn,Import_Emp_No,[Benefit_Terminate], [Show_Age], [Change_IP], [IsNewHired])
            SELECT M.Employer_Id,M.LASTNAME,M.FIRSTNAME,UPPER(LEFT(ISNULL(M.FIRSTNAME,'A'),1)+''+LEFT(ISNULL(M.LASTNAME,'C'),1))+CONVERT(VARCHAR,MaxImportEENumber+ROWNUM) [UserName],RIGHT(NEWID(),10) [UserPass],ISNULL(M.Location_Id,0)[Location_Id],M.Level2code,M.Level3code,M.LEVEL4Code,M.LEVEL5Code,M.DATEOFHIRE,
            1 [JobStatus],SSN,M.MaxImportEENumber+ROWNUM [ImportEmpNo],0 [Benefit_Terminate], 0[Show_Age],M.ChangeIP,1 [IsNewHired] from(
            SELECT ROW_NUMBER() OVER(ORDER BY [FIRSTNAME]) AS ROWNUM,* FROM
            (
                    SELECT DISTINCT @Employer_Id [Employer_Id],PR.LASTNAME,PR.FIRSTNAME,(SELECT  MAX(CAST(ISNULL(Employee_Id,0) as BIGINT)) from Tbl_Basicinfo) [MaxImportEENumber],
                    (SELECT TOP 1 Location_Id from Tbl_Location where Location_Code=PR.LEVEL1CODE and Employer_Id=@Employer_Id and Location like '%'+PR.LEVEL1DESCRIPTION+'%')  [Location_Id],
                    (SELECT TOp 1 LEVEL2Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL2Code,
                    (SELECT TOp 1 LEVEL3Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL3Code,
                    (SELECT TOp 1 LEVEL4Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL4Code,
                    (SELECT TOp 1 LEVEL5Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL5Code,
                    (SELECT MIN(DATEOFHIRE) from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) DATEOFHIRE,1 Job_Status,PR.SSN,PR.ChangeIP

                        from Tbl_PPACA_Import PR
                        LEFT OUTER JOIN Tbl_Basicinfo L ON L.ssn=PR.SSN and L.Employer_Id=PR.EmployerId
                        WHERE PR.EmployerId=@Employer_Id and L.SSN is null and ISNULL(PR.SSN,'') not like '' AND ISNUMERIC(PR.SSN)=1
                        and ISNULL(PR.PPE,DATEOFHIRE) =(SELECT MAX(ISNULL(PPE,DATEOFHIRE)) FROM Tbl_PPACA_Import I WHERE I.SSN=PR.SSN AND I.EmployerId=PR.EmployerId )

                    ) V
            ) M;

注意:有人可以帮助我吗?我从上面的查询中收到此错误:

Note: Can some one help me I am receiving this error from above query:

错误 515:无法将值 NULL 插入列 'UserName'、表 'ppacahelper.dbo.Tbl_Basicinfo'；列不允许空值.插入失败.行:131 spImportPPACAREPORT

任何帮助都将受到高度赞赏

Any help will highly be apprecieted

推荐答案

我认为您需要将 ISNULL 添加到您为用户名值连接的所有列中:

I think you need to add ISNULL to all the columns you are concatenating for username value:

INSERT INTO Tbl_Basicinfo(Employer_Id,Lname,Fname,UserName,Userpass,Location_Id,cc2,CC3,cc4,cc5,Dohire,Job_Status,ssn,Import_Emp_No,[Benefit_Terminate], [Show_Age], [Change_IP], [IsNewHired])
        SELECT M.Employer_Id,M.LASTNAME,M.FIRSTNAME,UPPER(LEFT(ISNULL(M.FIRSTNAME,'A'),1)+''+LEFT(ISNULL(M.LASTNAME,'C'),1))+CONVERT(VARCHAR,ISNULL(MaxImportEENumber,0)+ISNULL(ROWNUM,0)) [UserName],RIGHT(NEWID(),10) [UserPass],ISNULL(M.Location_Id,0)[Location_Id],M.Level2code,M.Level3code,M.LEVEL4Code,M.LEVEL5Code,M.DATEOFHIRE,
        1 [JobStatus],SSN,M.MaxImportEENumber+ROWNUM [ImportEmpNo],0 [Benefit_Terminate], 0[Show_Age],M.ChangeIP,1 [IsNewHired] from(
        SELECT ROW_NUMBER() OVER(ORDER BY [FIRSTNAME]) AS ROWNUM,* FROM
        (
                SELECT DISTINCT @Employer_Id [Employer_Id],PR.LASTNAME,PR.FIRSTNAME,(SELECT  MAX(CAST(ISNULL(Employee_Id,0) as BIGINT)) from Tbl_Basicinfo) [MaxImportEENumber],
                (SELECT TOP 1 Location_Id from Tbl_Location where Location_Code=PR.LEVEL1CODE and Employer_Id=@Employer_Id and Location like '%'+PR.LEVEL1DESCRIPTION+'%')  [Location_Id],
                (SELECT TOp 1 LEVEL2Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL2Code,
                (SELECT TOp 1 LEVEL3Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL3Code,
                (SELECT TOp 1 LEVEL4Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL4Code,
                (SELECT TOp 1 LEVEL5Code from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) LEVEL5Code,
                (SELECT MIN(DATEOFHIRE) from Tbl_PPACA_Import where ssn=PR.SSN and EmployerId=PR.EmployerId) DATEOFHIRE,1 Job_Status,PR.SSN,PR.ChangeIP

                    from Tbl_PPACA_Import PR
                    LEFT OUTER JOIN Tbl_Basicinfo L ON L.ssn=PR.SSN and L.Employer_Id=PR.EmployerId
                    WHERE PR.EmployerId=@Employer_Id and L.SSN is null and ISNULL(PR.SSN,'') not like '' AND ISNUMERIC(PR.SSN)=1
                    and ISNULL(PR.PPE,DATEOFHIRE) =(SELECT MAX(ISNULL(PPE,DATEOFHIRE)) FROM Tbl_PPACA_Import I WHERE I.SSN=PR.SSN AND I.EmployerId=PR.EmployerId )

                ) V
        ) M;

这篇关于Sql存储过程中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！