USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalRegisters int output
As

BEGIN 
    SET NOCOUNT ON 
 
    DECLARE 
        @Max_row int, 
        @First_row int 
 
    SET @Max_row = @PageSize * @CurrentPage  
    SET @First_row = @Max_row - (@PageSize - 1)  


    Declare @Date As datetime
    Declare @Id As bigint
    Declare @City As varchar(100)

   
 SET ROWCOUNT @First_row 
 
    Select @Date = TheDate, @Id = Offer_id, @City = city.City_name
    From Offers As Offe
    JOIN Users As us
    On Offe.User_num = us.User_id
    JOIN Cities As city
    On Offe.city_num = city.City_id           
    Where city.City_name = 'Paris'
    Order by TheDate Desc, Offer_id Desc 


    SELECT @TotalRegisters = COUNT(*) FROM Offers As Offe
    JOIN Users As us
    On Offe.User_num = us.User_id
    JOIN Cities As city
    On Offe.city_num = city.City_id   
    Where city.City_name = ‘Paris'

    SET ROWCOUNT @PageSize



    Select Offer_id, TheDate, Title, us.Company_name, city.City_name
    From Offers As Offe 
    JOIN Users As us
    On Offe.User_num = us.User_id
    JOIN Cities As city
    On Offe.city_num = city.City_id

    Where ((TheDate < @Date) Or (TheDate = @Date And Offer_id <= @Id)) 
    And city.City_name = 'Paris'
    Order by TheDate Desc, Offer_id Desc
    SET ROWCOUNT 0 

 SET NOCOUNT OFF    
END 
GO