CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2005cursor
HEEN
HEEN

Reputation: 4721

Stored procedure not returning any data for the date passed

I have a stored procedure 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 stored procedure like this, I do not get any rows returned 

exec UserReportData '20160610'

but in the table I do have rows with that date.

Like running below query

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

So, why are there no records returned when executing the stored procedure?

Upvotes: 1

Views: 277

Answers (1)

Squirrel
Squirrel

Reputation: 24763

because your u_datetime column contains date & time

one way is to handle in the WHERE clause

and a.U_datetime >= @As_ONDATE
AND a.U_datetime <  DATEADD(DAY, 1, @As_ONDATE)

Also you might want to consider converting your query not to use Cursor

Upvotes: 5

Related Questions