无法将空值插入到列"用户名"，表"tempdb.dbo.#temptable 错误

Question

>我有一个SP.在执行时出现错误，因为

无法将值 NULL 插入到列"用户名"表"tempdb.dbo.#temptable__________________________________________________________________________________________________________0000000002FD"中;列不允许空值。更新失败。 该语句已终止。

下面是我的SP

ALTER PROCEDURE [dbo].[UserReportData] 
                @As_ONDATE Datetime 
                AS 
            BEGIN 
                    DECLARE @REPORTDATE datetime        
                    DECLARE @USERNAME varchar(110)      
            Select * INTO #temptable
        FROM
                    (
                        select  a.CUser_id, b.User_Id, a.U_datetime
                        as REPORTDATE, b.first_Name + ' '  + b.last_name as USERNAME
                        from inward_doc_tracking_trl a inner join user_mst b
                        on a.CUser_id = b.mkey
                        and a.U_datetime >= @As_ONDATE
                ) as x
DECLARE Cur_1 CURSOR
            FOR SELECT CUser_id, User_Id FROM #temptable
                    OPEN Cur_1
                        DECLARE @CUser_id INT
                        DECLARE @User_Id INT
                        FETCH NEXT FROM Cur_1 
                        INTO @CUser_id, @User_Id
                        WHILE (@@FETCH_STATUS = 0)
            BEGIN
                            SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl                        
                            where  U_datetime >= @As_ONDATE                             

                            UPDATE #temptable
                                SET REPORTDATE = @REPORTDATE,
                                    USERNAME = @USERNAME
                                WHERE CUser_id = @CUser_id
                                AND User_Id = @User_Id  

                FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
                    END
            CLOSE Cur_1
            DEALLOCATE Cur_1

            SELECT * FROM #temptable
                        DROP TABLE #temptable                           
    END

更新的程序

ALTER PROCEDURE [dbo].[UserReportData] 
                @As_ONDATE Datetime 
                AS 
            BEGIN 
                    DECLARE @REPORTDATE datetime        
                    DECLARE @USERNAME varchar(110)      
            Select * INTO #temptable
        FROM
                    (
                        select  a.CUser_id, b.User_Id, a.U_datetime
                        as REPORTDATE, ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') AS USERNAME                         
                        from inward_doc_tracking_trl a inner join user_mst b
                        on a.CUser_id = b.mkey
                        and a.U_datetime >= @As_ONDATE
                ) as x
DECLARE Cur_1 CURSOR
            FOR SELECT CUser_id, User_Id FROM #temptable
                    OPEN Cur_1
                        DECLARE @CUser_id INT
                        DECLARE @User_Id INT
                        --DECLARE @USERNAME VARCHAR (100)
                        FETCH NEXT FROM Cur_1 
                        INTO @CUser_id, @User_Id
                        WHILE (@@FETCH_STATUS = 0)
            BEGIN
                            SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl                        
                            where  U_datetime >= @As_ONDATE 
                            SELECT @USERNAME = ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') 
                                FROM inward_doc_tracking_trl a 
                                INNER JOIN user_mst b on a.CUser_id = b.mkey 
                                where a.U_datetime >= @As_ONDATE                            
                            UPDATE #temptable
                                SET REPORTDATE = @REPORTDATE,
                                    USERNAME = @USERNAME
                                WHERE CUser_id = @CUser_id
                                AND User_Id = @User_Id  

                FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
                    END
            CLOSE Cur_1
            DEALLOCATE Cur_1

            SELECT * FROM #temptable
                        DROP TABLE #temptable                           
    END

Answer 1

首先注释初始BEGIN块中的@USERNAME声明

 -- DECLARE @USERNAME varchar(110)

然后处理USERNAME的NULL值，如SELECT * INTO块中所示

 ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') AS USERNAME

然后将光标中的@USERNAME添加为

DECLARE Cur_1 CURSOR
        FOR SELECT CUser_id, User_Id, USERNAME FROM #temptable
            OPEN Cur_1
                    DECLARE @CUser_id INT
                    DECLARE @User_Id INT
                    DECLARE @USERNAME VARCHAR (200) -- can set your required length
                    FETCH NEXT FROM Cur_1 
                    INTO @CUser_id, @User_Id

然后@USERNAME不会抛出NULL错误

---

更新：

根据您的评论，我更新了答案：

因此，您希望根据U_datetime >= @As_ONDATE条件获得@USERNAME。

因此，使用您现有的代码并在 CURSOR 中添加另一个块，以获取@USERNAME值，如SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl where U_datetime >= @As_ONDATE

SELECT @USERNAME = ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') -- no need of column alias here
FROM inward_doc_tracking_trl a 
INNER JOIN user_mst b on a.CUser_id = b.mkey 
WHERE a.U_datetime >= @As_ONDATE

Answer 2

也许尝试这个：

ALTER PROCEDURE [dbo].[UserReportData] 
@As_ONDATE Datetime 
AS 
BEGIN 
    Select * INTO #temptable
    FROM
    (
        select  
            a.CUser_id, 
            b.User_Id, 
            a.U_datetime as REPORTDATE, 
            ISNULL( b.first_Name + ' '  + b.last_name, 'NONAME' ) as USERNAME -- normally if one of the two is null then all of it is Null
        from inward_doc_tracking_trl a inner join user_mst b
        on a.CUser_id = b.mkey
        and a.U_datetime >= @As_ONDATE
    ) as x

    DECLARE @REPORTDATE datetime 
    -- moved out of the loop
    SELECT @REPORTDATE = U_datetime 
    FROM inward_doc_tracking_trl                        
    where  U_datetime >= @As_ONDATE -- this might bring more than one rows I think ???????

    DECLARE Cur_1 CURSOR FOR SELECT CUser_id, User_Id, USERNAME FROM #temptable
    DECLARE @CUser_id INT
    DECLARE @User_Id INT    
    DECLARE @USERNAME varchar(110) 
    OPEN Cur_1    
    FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id, @USERNAME
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        UPDATE #temptable SET 
            REPORTDATE = @REPORTDATE,
            USERNAME =  @USERNAME
        WHERE 
            CUser_id = @CUser_id
            AND User_Id = @User_Id  
        FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id, @USERNAME
    END
    CLOSE Cur_1
    DEALLOCATE Cur_1
    SELECT * FROM #temptable
    DROP TABLE #temptable                           
END