vendredi 10 juin 2016

Not able to execute the stored procedure

I have table in which my date column value is saved with time also

like this 2016-06-10 14:56:11.000

Now while executing my SP, I pass one parameter as date like this exec UserReportData '06-10-2016' but it is not showing any records. As it has 4 records in the table.

why?

UPDATE

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 = CONVERT(varchar(10), @As_ONDATE, 103)
                ) 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 = CONVERT(varchar(10), @As_ONDATE, 103)

                            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

Aucun commentaire:

Enregistrer un commentaire