How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:
DECLARE @count int
SET @count = 20
SELECT TOP @count * FROM SomeTable
This question is related to
sql
sql-server-2005
The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
Oisin.
(edited to replace @@rowcount with rowcount - thanks augustlights)
Its also possible to use dynamic SQL and execute it with the exec command:
declare @sql nvarchar(200), @count int
set @count = 10
set @sql = N'select top ' + cast(@count as nvarchar(4)) + ' * from table'
exec (@sql)
In x0n's example, it should be:
SET ROWCOUNT @top
SELECT * from sometable
SET ROWCOUNT 0
The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
Oisin.
(edited to replace @@rowcount with rowcount - thanks augustlights)
declare @rows int = 10
select top (@rows) *
from Employees
order by 1 desc -- optional to get the last records using the first column of the table
The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
Oisin.
(edited to replace @@rowcount with rowcount - thanks augustlights)
Or you just put the variable in parenthesis
DECLARE @top INT = 10;
SELECT TOP (@Top) *
FROM <table_name>;
The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
Oisin.
(edited to replace @@rowcount with rowcount - thanks augustlights)
Its also possible to use dynamic SQL and execute it with the exec command:
declare @sql nvarchar(200), @count int
set @count = 10
set @sql = N'select top ' + cast(@count as nvarchar(4)) + ' * from table'
exec (@sql)
declare @rows int = 10
select top (@rows) *
from Employees
order by 1 desc -- optional to get the last records using the first column of the table
Or you just put the variable in parenthesis
DECLARE @top INT = 10;
SELECT TOP (@Top) *
FROM <table_name>;
Its also possible to use dynamic SQL and execute it with the exec command:
declare @sql nvarchar(200), @count int
set @count = 10
set @sql = N'select top ' + cast(@count as nvarchar(4)) + ' * from table'
exec (@sql)
In x0n's example, it should be:
SET ROWCOUNT @top
SELECT * from sometable
SET ROWCOUNT 0
In x0n's example, it should be:
SET ROWCOUNT @top
SELECT * from sometable
SET ROWCOUNT 0
Source: Stackoverflow.com