Invalid column name SQL Server 2012
sql, sql-server, sql-server-2012, t-sql
Solution
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
Problem
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 ```