I have this query and I want to get “page” variable which is created:
SELECT *, (rowNum / 5) + 1 as page FROM ( SELECT *, Row_Number() OVER (ORDER BY items.id_item ASC) as rowNum FROM items INNER JOIN utilizadores ON items.autor = utilizadores.id_user ) x WHERE (rowNum / 1000) + 1 = 1
How can I get it in the query to make a procedure to search.
CREATE PROCEDURE get_items_by_page @page int AS SELECT *, (rowNum / 5) + 1 as page FROM ( SELECT *, Row_Number() OVER (ORDER BY items.id_item ASC) as rowNum FROM items INNER JOIN utilizadores ON items.autor = utilizadores.id_user ) x WHERE (rowNum / 1000) + 1 = 1 //the required condition to query is in bottom GO
and page = @page;
Advertisement
Answer
CREATE PROCEDURE get_items_by_page @page int AS SELECT x.*, (rowNum / 5) + 1 as page FROM ( SELECT *, Row_Number() OVER (ORDER BY items.id_item ASC) as rowNum FROM items INNER JOIN utilizadores ON items.autor = utilizadores.id_user ) x WHERE (rowNum / 5) + 1 = @page GO