[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

sql server stored procedure row_number help

Hello experts,

I have the following stored procedure;-

SELECT TOP 15 * FROM
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records
     ) AS a
     WHERE num >= @start

But I'm needing to change the value 15 on  this line --> SELECT TOP 15 * FROM
to a variable, like so for instance;-

SELECT TOP @top * FROM

But I get errorMsg 102, Level 15, State 1, Procedure sp_GetRecord, Line 22
Incorrect syntax near '@top'.

Is this not possible or am I doing it wrong.

Many thanks,

Clara
0
claracruz
Asked:
claracruz
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT TOP (@n) * FROM
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you get that syntax error, then either
* you are connected to a sql 2000 server/database
* you are connected to a sql 2005 server, but the database is in sql 2000 (or lower) compatibility.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
anyhow, you could do this:
SELECT * FROM 
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records
     ) AS a
     WHERE num >= @start
       AND num < @start + @top

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now