Skip to content
Advertisement

SQL query how to get the local variable

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement