samedi 11 juin 2016

Cannot insert NULL values into column 'USERNAME', table 'tempdb.dbo.#temptable error

I have a SP. while executing I am getting error as

Cannot insert the value NULL into column 'USERNAME', table 'tempdb.dbo.#temptable__________________________________________________________________________________________________________0000000002FD'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Below is my 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

Aucun commentaire:

Enregistrer un commentaire