Invalid column name SQL Server 2012

Question

What do I have to do to use name Rowss in the WHERE clause ?

SELECT TOP 10 
    ROW_NUMBER() OVER(ORDER BY dp.IdPytanie) AS Rowss,
    dp.IdPytanie
    ,dp.SpecjalnePytanie
 FROM dodajtemat_pytanie dp
 WHERE 
    (@RowBegining = 0 OR convert(int,Rowss) >= @RowBegining)
    AND (@RowEnd = 0 OR Rowss <= @RowEnd)

Error

This work ->

 @RowEnd = 0 OR ROW_NUMBER() OVER(ORDER BY dp.IdPytanie) <= @RowEnd

Answer 1

Use a subquery or a CTE like this:

WITH CTE
AS
(
    SELECT 
      ROW_NUMBER() OVER(ORDER BY dp.IdPytanie) AS Rowss,
      dp.IdPytanie,
      dp.SpecjalnePytanie
    FROM dodajtemat_pytanie dp
)
SELECT *
FROM CTE
WHERE (@RowBegining = 0 OR Rowss >= @RowBegining)
  AND (@RowEnd      = 0 OR Rowss <= @RowEnd);

The WHERE clause is logically evaluated before the SELECT statement, so that it doesn't recognize that newly created alias Rowss.

Fore more information about the logical query processing steps in SQL Server, see:

• Logical Query Processing Poster by Itzik Ben