vendredi 10 juin 2016

Stored procedure not returning any data for the date passed

I have a SP, which takes one parameter as DATE. which will return the data for that date from the table.

ALTER PROCEDURE [dbo].[UserReportData] 
                @As_ONDATE Datetime 

                AS 
            BEGIN 
                    DECLARE @REPORTDATE datetime        
                    --DECLARE @OPENING INT      

            Select * INTO #temptable
        FROM
                    (
                        select  a.CUser_id, b.User_Id, a.U_datetime
                        as REPORTDATE
                        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 U_datetime 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
                                and 

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


            SELECT * FROM #temptable
                        DROP TABLE #temptable                           
    END

When I execute the SP, I do not get any rows returned by executing exec UserReportData '20160610'

but in table i have dates. like running below query

SELECT U_datetime FROM inward_doc_tracking_trl  where  u_datetime = '2016-06-10 14:56:11.000'

So, why there is no record shown while executing the SP

Aucun commentaire:

Enregistrer un commentaire