Re: TOP statement doesn't accept a variable

From: alien2_51 (dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 13:12:55 -0700

DECLARE @rowcount INT
SET @rowcount = 1
SET ROWCOUNT @rowcount
SELECT * FROM dbo.Table_Name

"Derek Ruesch" <anonymous@discussions.microsoft.com> wrote in message
news:0f0b01c4a016$79642f30$a601280a@phx.gbl...
> 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

  • Re: Can @@ROWCOUNT return NULL?
    ... from the stored procedure. ... > Immediately after each statement I save the value returned by @@ROWCOUNT to ... it appears that in SQL Server ... This is not the case here - the only DML queries are INSERTs into ...
    (comp.databases.ms-sqlserver)
  • Re: Stored procedure question
    ... Thank you Narayana ... > You could capture the rowcount after this, ... > I need to write a stored procedure which changes user password based on ... > validity of his old password. ...
    (microsoft.public.sqlserver.programming)
  • Re: Error handling in stored procedure AND checking
    ... Just because your SQL statement attempts to update ... If you really do want to get an error when invalid SSN update is attempted, ... The @@ROWCOUNT function returns the number of rows affected by the last ... Inside the stored procedure, just after the UPDATE check if rows have ...
    (comp.databases.ms-sqlserver)
  • Can @@ROWCOUNT return NULL?
    ... I am troubleshooting a relatively large stored procedure with ... Immediately after each statement I save the value returned by @@ROWCOUNT to ... Before doing any further troubleshooting, I would like to rule out the ... it appears that in SQL Server ...
    (comp.databases.ms-sqlserver)
  • Re: How can I assign the result of dateadd to a variable ?
    ... Yes, by setting rowcount to 2, every statement that follows it can only ... If you run this from a stored procedure, ... run this from a script, however, you should set rowcount back to 0 ... before the end of the script to avoid affecting other SQL statements. ...
    (microsoft.public.sqlserver.programming)