ORDER BY in a SP

I'm getting a little frustrated... I'm trying to write a stored proc... the
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:

> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>