CODEWITHSUNDEEP
sql-servert-sql
Nitish
Nitish

Reputation: 23

Why do variables not work with TOP command?

  1. Why does below SQL script throw errors?
  2. Is this issue with TOP command?
  3. Or is it because of the SELECT statement which does not support SQL variables?

My code:

DECLARE @cnt INTEGER = 0;
Declare @cnt_total INT;

SET @cnt_total = 5

WHILE (@cnt < @cnt_total)
BEGIN
    SET @cnt = @cnt + 1;

    SELECT TOP @cnt * 
    FROM FOCUSCDR.PATIENTDXHISTORY
END;

Error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@cnt'.

While above works when used.

SELECT TOP 1 * 
FROM FOCUSCDR.PATIENTDXHISTORY

Upvotes: 1

Views: 83

Answers (1)

Rigerta
Rigerta

Reputation: 4039

Because the syntax requires parentheses: 

DECLARE @cnt INTEGER = 0;
Declare @cnt_total int ;

Set @cnt_total = 5

WHILE (@cnt < @cnt_total)
BEGIN
   SET @cnt = @cnt + 1;
   Select  top (@cnt) * from FOCUSCDR.PATIENTDXHISTORY
END;

Edit:

As mentioned in the comment by @Tim Biegeleisen, you should be using ORDER BY, otherwise it makes no sense using TOP since the order will not be guaranteed and you might get non consistent results. Check out this article for more info on it.

Upvotes: 3

Related Questions