vendredi 10 juin 2016

Not getting proper value in temp table

I have created an SP, which brings me the data from the inward_doc_tracking_trl table.

Here is my SP,

ALTER PROCEDURE [dbo].[UserReportData] 
                @As_ONDATE Datetime 
                AS 
            BEGIN 
    DECLARE @REPORTDATE datetime        
    DECLARE @OPENING INT        

            Select * INTO #temptable
        FROM
                    (select distinct  a.CUser_id, b.User_Id,a.U_datetime as REPORTDATE, b.first_name + ' ' + b.last_name AS USERNAME,
                        0 OPENING,  0 TOTAL_DOCUMENT, 0 INWARD, 0 FIRST_LEVEL_PROCESSING, 0 DATA_ENTRY
                        from inward_doc_tracking_trl a, user_mst b
                        where 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  FROM inward_doc_tracking_trl                        
                 where U_datetime = @As_ONDATE 

                            UPDATE #temptable
                            SET REPORTDATE = @REPORTDATE
                            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



 -- exec UserReportData '2016-06-10' 

So my issue is when I execute the Stored procedure UserReportData with today's date

I get REPORTDATA column as blank. but there is a row in the table for that date.

kindly know where i am going wrong

Aucun commentaire:

Enregistrer un commentaire