N K N K - 5 months ago 9
SQL Question

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?

Answer

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