Using variables with TOP 1

[DomTrix]

Hello there,

I am writing a stored procedure that is a simple select query. I want the calling code to supply a maximum number of rows to be returned but I cannot get the code to work using this parameter. Here is my code:

CREATE PROCEDURE dbo.selFeaturedItems
@maxItems	int

 AS

SELECT TOP @maxItems
		pro_ID,
		pro_Title,
		pro_Text
FROM product

...
...
RETURN

When i check syntax, I get error 170, incorrect syntax on the line that contains 'SELECT TOP @maxItems'. If I replace @maxItems with a literal value the code checks out fine.

Could anyone show me my mistake?

Thanks in advance

DT

 

[sunila7]

In SQL 2000 TOP doesnt take a variable as a parameter, this has changed in SQL 2005.

So, you could try this alternate solution:

CREATE PROCEDURE dbo.selFeaturedItems
@maxItems    int

 AS
SET ROWCOUNT @maxItems 
SELECT 
        pro_ID,
        pro_Title,
        pro_Text
FROM product

...
...

SET ROWCOUNT 0
RETURN

Sunil

 

[DomTrix]

Great, thanks for the lightning reply

I suspected as much but had no idea of an alternative.

dt