Using a @parameter within the TOP clause

[Adam]

Hi guys.

 

This should be a pretty straight-forward question for any T-SQL developer; how can I use a parameter passed to a stored procedure within the TOP clause of a SELECT statement?

 

For example:

 

SELECT TOP @Length * FROM vanity_urls
ORDER BY id ASC;

 

Unfortunately this produces the error: "Incorrect syntax near '@Length'".

 

Anybody have any ideas?

 

Thanks

 

Edit:

 

To add I'm using SQL Server 2005, and tried the suggestion here of wrapping the parameter in brackets, however that didn't work..?

[Adam]

Made a grave error, I'm working from a SQL Server 2000 box. What would be the best alternative in that case then?

 

Thanks

[Adam]

Cheers for all the suggestions

 

Found a solution though, but it's a little hacky in that you have to set the 'rowcount' manually and declare a table variable with an auto-incrementing key.

 

Example:

 

declare @vu_temp table (
    row_count	smallint identity(1,1),
    id		    smallint,
);

insert into @vu_temp (id)
    select * from (...);

-- optional to select the total count
select count(id) as count from @vu_temp;

-- limit the number of records returned
set rowcount @LengthParameter;

select vu.* from vanity_urls vu, @vu_temp tmp
where vu.id = tmp.id
-- set the offset
and tmp.row_count >= @OffsetParameter;