mardi 28 juin 2016

Create a Cursor and loop all users data

I have a Stored procedure which gives me records like below.

SC

Now what I want is. I want to create a CURSOR for the SP which loops for every user and fetch the records.

Below is my SP.

ALTER PROCEDURE GET_INWARD_REMINDER_REPORT
            AS
            BEGIN

                      Select distinct
                          U.first_name + ' ' + U.last_name UserName,
                    TH.*, 
                CASE 
                        WHEN TL.U_datetime < DATEADD(d, -5, GETDATE()) 
                        THEN M.Reporting_To
                            ELSE NULL 
                            END Reporting_1
                        from inward_doc_tracking_trl TL
                        INNER JOIN inward_doc_tracking_hdr TH
                            ON TH.mkey = TL.ref_mkey
                        INNER JOIN user_mst U
                            ON TH.User_Id = U.mkey
                        INNER JOIN emp_mst M
                            ON M.mkey = U.employee_mkey
                        where TL.NStatus_flag NOT IN (5,14)
                        and TL.U_datetime < DATEADD(d, -3, GETDATE())
            END
GO

Kindly let me know how to do this.

Aucun commentaire:

Enregistrer un commentaire