Dynamic SELECT TOP var In SQL Server

Question

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

User · Answer

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

User · Answer

In x0n s example  it should be   SET ROWCOUNT  top  SELECT   from sometable  SET ROWCOUNT 0   http   msdn microsoft com en-us library ms188774 aspx

User · Answer

SELECT TOP   count    FROM SomeTable   This will only work with SQL 2005

User · Answer

SELECT TOP   count    FROM SomeTable   This will only work with SQL 2005

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Or you just put the variable in parenthesis  DECLARE  top INT   10   SELECT TOP   Top    FROM  lt table name gt

User · Answer

Or you just put the variable in parenthesis  DECLARE  top INT   10   SELECT TOP   Top    FROM  lt table name gt

User · Answer

In x0n s example  it should be   SET ROWCOUNT  top  SELECT   from sometable  SET ROWCOUNT 0   http   msdn microsoft com en-us library ms188774 aspx

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In x0n s example  it should be   SET ROWCOUNT  top  SELECT   from sometable  SET ROWCOUNT 0   http   msdn microsoft com en-us library ms188774 aspx

User · Answer

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

User · Answer

SELECT TOP   count    FROM SomeTable   This will only work with SQL 2005

User · Answer

SELECT TOP   count    FROM SomeTable   This will only work with SQL 2005

User · Answer

In x0n s example  it should be   SET ROWCOUNT  top  SELECT   from sometable  SET ROWCOUNT 0   http   msdn microsoft com en-us library ms188774 aspx

[sql] Dynamic SELECT TOP @var In SQL Server

Examples related to sql

Examples related to sql-server-2005