samedi 11 juin 2016

How to run LOOP in a cursor for each user

I have a SP, which gives me two records (2 username) like below

SP loop

Now,

what I want is to loop each user using cursor and display its data from the table for the date which is passed as in the parameter

Below is my SP.

ALTER PROCEDURE GET_USER_REPORT_DATA
        @From_Date datetime,
        @To_Date datetime

    AS
    BEGIN

        Select * into #GetUserTable
            from
                (
                      select distinct a.N_UserMkey, b.mkey,   
                      b.first_name + ' ' + b.last_name NAME from inward_doc_tracking_trl a inner join user_mst b on 
                      a.N_UserMkey = b.mkey 
                      where a.U_datetime between @From_Date and @To_Date                  
                ) as xx     

    DECLARE @N_Usermkey int, @mkey int, @NAME varchar       
        DECLARE UserCursor CURSOR FOR  
            select distinct a.N_UserMkey, b.mkey,   
                      b.first_name + ' ' + b.last_name NAME from inward_doc_tracking_trl a inner join
                      user_mst b on a.N_UserMkey = b.mkey 
                      where a.U_datetime between @From_Date and @To_Date
        OPEN UserCursor

    FETCH NEXT FROM vendor_cursor   
            INTO @N_Usermkey, @mkey, @NAME 

        -- need to run loop for each user

            SELECT * FROM #GetUserTable
                         DROP TABLE #GetUserTable
    END

GO

I am unaware on how to use cursor kindly let me know

Aucun commentaire:

Enregistrer un commentaire