TOP statement doesn't accept a variable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Derek Ruesch (anonymous_at_discussions.microsoft.com)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 13:06:25 -0700

The following stored procedure is designed to return the
given row number from a query.

CREATE PROCEDURE returnrownumber @rownumber INT AS

SELECT TOP 1 Name
FROM (SELECT TOP @rownumber Name
      FROM tblEmployee
      ORDER BY Name) DerivedTbl
ORDER BY Name DESC

However when I run this procedure the following error is
returned: "Incorrect syntax near the keyword '@rownumber'.
Incorrect syntax near the keyword 'ORDER'." It looks like
the use of a variable is not allowed in the TOP clause of
a select statement. If this is the case is there another
way to make this stored procedure work?

I greatly appreciate any help. Thanks.

Derek Ruesch
 

Relevant Pages

  • How do I create a Stored Precedure if one does not exist?
    ... I am trying to create a stored procedure if one does not exist, ... Error message: ... Incorrect syntax near the keyword 'PROCEDURE'. ...
    (microsoft.public.sqlserver.programming)
  • CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
    ... I create one stored procedure to return some objects, ... When i execute the procedure with no values the default parameters are used ... Incorrect syntax near the keyword 'user_name'. ...
    (microsoft.public.sqlserver.server)
  • Re: TOP statement doesnt accept a variable
    ... "Derek Ruesch" wrote in message ... > The following stored procedure is designed to return the ... "Incorrect syntax near the keyword '@rownumber'. ...
    (microsoft.public.sqlserver.programming)
  • ORDER BY CASe
    ... I am creating a stored procedure to return a sorted list ... Now i would like to add 'ASC' or 'DESC' like follows ... But this return an error 'Incorrect syntax near the keyword 'ASC'.' ...
    (microsoft.public.sqlserver.server)
  • Re: vb.net 2005 - OO question...
    ... dim oSys as new System_Data ... stored procedure that returns all of the fields in that table as output ... "sub new" will run AGAIN and I will be in an infinite loop... ... You are already inside the object, and you use the Me keyword ...
    (microsoft.public.dotnet.languages.vb)