I have this query and I want to get “page” variable which is created:
x
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