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