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 enter image description here

This work ->

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

You cannot reference in a WHERE clause an alias given to a column of the same query, you need to go with a subquery, or replace Rowss with its full expression. — A Hocevar, Mar 13 '13 at 12:49
As you are on 2012 you might want to look at [`OFFSET ... FETCH`](http://stackoverflow.com/a/5620802/73226) — Martin Smith, Mar 13 '13 at 13:42

Mahmoud Gamal · Accepted Answer · 2013-03-13T13:00:11.953

7

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

edited Mar 13 '13 at 13:00

answered Mar 13 '13 at 12:47

Mahmoud Gamal

78,257
17
139
164

but I will lost a lot in optimization – Rafał Developer Mar 13 '13 at 12:53
because I take all arguments and later I take only this which I want. Take all arguments is mistake because procedure will be slower. Of course your answer is correct and I will accept this. Tahnks for help and your time. – Rafał Developer Mar 13 '13 at 12:58
1

And why are you doing the conversion? `convert(int, Rowss)` – ypercubeᵀᴹ Mar 13 '13 at 12:58
@Mahmoud Gamal ok I will test this. – Rafał Developer Mar 13 '13 at 13:00
@ypercube this was only example, I know Rowss is int :) – Rafał Developer Mar 13 '13 at 13:00
@ypercube - Sorry, I just copied it from the question. Fixed now. – Mahmoud Gamal Mar 13 '13 at 13:00
2

@Rafael-JuniorMVCDeveloper why do you think a CTE will optimize differently than the query you originally wrote (if that were legal syntax)? – Aaron Bertrand Mar 13 '13 at 13:48

Invalid column name SQL Server 2012

1 Answers1