Select Top in SQL Server??

[Guest_imported]

I have a query n Access97 that I need to convert to SQL Server as a stored procedure, it looks like this:

CREATE PROCEDURE TopAlRecs @recs VARCHAR(50) AS
SELECT TOP @recs *
FROM AlarmPage
ORDER BY AlarmPageID DESC;

But when I try to "run" this stored procedure in Query Analyser I get the following error:
Incorrect syntax near '@recs'

If I put in a number like:
SELECT TOP 3 *

then it works fine. But I need to have different numbers there everytime so I need to send a parameter @rec. How can I solve this problem?

I hope someone can help me!

regards, Bob

 

[tcorrigan]

I believe the answer is to issue the command SET ROWCOUNT @recs before your select statement (without the TOP x qualifier) and then SET ROWCOUNT 0 immediately after. Make sure to check the documentation on SET ROWCOUNT as there are some caveats about using it. I have not actually tested this!

 

[elmo29]

I had exactly the same trouble with TOP, it doesn't even seem to work if you put it in a string and then use EXEC. The only way which works for me is the SET ROWCOUNT @value,
THIS DOES WORK

 

[foxdev]

If it compiles OK, but fails to run, could it be that @rows is declared as VarChar rather than Integer?

 

[elmo29]

No mine is declared as integer. Here is my code if that is any help. I am very much a novice so I'm afraid I can't help too much

You could try this.....

CREATE PROCEDURE TopAlRecs @recs int AS

SET ROWCOUNT @recs

SELECT alarmpage.*
FROM AlarmPage

And see whether that works

 

[Guest_imported]

I have tried quite a few different ways to get the TOP statement to work but it won't work if I want to pass a parameter to it.

I am now using the ROWCOUNT @value instead and it works okay for me so far. I read the "disclaimer" in 'Books Online' that ROWCOUNT should only be used if you really need to...but whatever, it works okay now I suppose

Thanks for all your help!