Setting an SQL Query AS a variable or parameter (Integer prefered) and using it

Question

Hello All,

I couldn't solve that mistery question in SQL SERVER.

Here an example that I tried to do and it didn't work.

DECLARE @Total int;
SET @Total = (SELECT COUNT(*)-10 FROM MYTABLE)

SELECT TOP @Total IdColumn FROM MYTABLE

How can I use the following query

SELECT COUNT(*)-10 FROM MYTABLE

as an integer variable, somewhere else in my code.

Answer 1

When you use a variable in a top clause, you need to use parenthesis, like this:

SELECT TOP (@Total) IdColumn FROM MYTABLE

If you are using SQL2000, you cannot use a variable in a top clause. If you try, you will get a parse/syntax error. Instead, you can use RowCount, like this:

DECLARE @Total int;
SET @Total = (SELECT COUNT(*)-10 FROM MYTABLE)

Set RowCount @Total
SELECT IdColumn FROM MYTABLE
Set RowCount 0

Answer 2

Use Brackets: The following works for me:

DECLARE @Total int;
SET @Total = (SELECT COUNT(*)-10 FROM MYTABLE)

SELECT TOP (@Total) IdColumn FROM MYTABLE