Using TOP with a variable
-
I have the following query in a stored procedure:
SELECT
TOP(@top) Fault, Occurrences, Duration
FROM line16l2.devDowntime
ORDER BY Occurrences
@top is a TINYINT parameter to the stored procedure.
This works fine in the development server (SQL Server 2005) but I can't create the procedure in the test server (2000, production will be 2000 too). I have compatibility level set to 80 on dev server. The message is: incorrect syntax near '('
I can use TOP with a hard-coded number (and no parentheses) but I can't figure out how to supply a variable. The user calling the stored procedure has no permissions on the underlying table, so I can't use dynamic SQL.
-
This is rather heavy-handed, but if you have a known number of TOP choices, you could use flow control....
DECLARE @Top tinyint
SET @Top = 2
IF @Top = 1
BEGIN
SELECT TOP 1 Fault, Occurrences, Duration FROM line16l2.devDowntime ORDER BY Occurrences
END
ELSE IF @Top = 2
BEGIN
SELECT TOP 2 Fault, Occurrences, Duration FROM line16l2.devDowntime ORDER BY Occurrences
END
ELSE IF @Top = 3
BEGIN
SELECT TOP 3 Fault, Occurrences, Duration FROM line16l2.devDowntime ORDER BY Occurrences
END
ELSE IF @Top = 4
BEGIN
SELECT TOP 4 Fault, Occurrences, Duration FROM line16l2.devDowntime ORDER BY Occurrences
END
ELSE
BEGIN
SELECT Fault, Occurrences, Duration FROM line16l2.devDowntime ORDER BY Occurrences
END
I wasn't born stupid - I had to study.
-
SQL2L doesnt let you parameterize Top. What you can do is set rowcount @Somevar before the select, then immediately do set rowcount 0 after the select. That syntax will work across SQL 7/2000/2005.
-
This is another way to do it:
-- This code will be parameter in procedure
declare @top tinyint
set @top = 10
-- Declare variable
declare @sql NVARCHAR(4000)
-- This is your code
SET @sql = 'SELECT TOP ' + CAST(@top AS VARCHAR) + ' Fault, Occurrences, Duration '
SET @sql = @sql + 'FROM line16l2.devDowntime '
SET @sql = @sql + 'ORDER BY Occurrences'
-- Print Query
PRINT @sql
-- Execute Query
EXEC SP_EXECUTESQL @sql
-
Thanks for the replies. The ROWCOUNT setting works.
I was a DBA for SQL6.5, left the field, and recently have been learning SQL2005. But I skipped 7 and 2000!! So I never know when features appeared.
-
S'ok... I've been with SQL Server since it was 6.5 and I keep wondering when the next fault will occur
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply