On Mon, 12 Apr 2004 16:45:26 +0100, John Morgan wrote:
In a 'Top n' type statement I wish to be able to insert the n value
from a parameter, within a stored precedure eg
Having declared @pageSize as a parameter I want to run the following
type of query :
SELECT DISTINCT TOP @pageSize routeID, routeName FROM
tblRoute_Heade r
When I attempt to do so I get an error mesage indicating incorrect
syntax. I do not get an error message if I specify 'n' directly as in
TOP 10
Am I missing something or is this not possible within a stored
procedure?
Best wishes, John Morgan
The TOP clause will only take an integer value, not a variable.
There are two other ways to limit your output to @pageSize rows:
1. Using proprietary syntax, not portable to other DBMS's
SET ROWCOUNT @pageSize
SELECT DISTINCT routeID, routeName
FROM tblRoute_Header
WHERE ...
ORDER BY ...
SET ROWCOUNT 0
Note 1: Don't forget to SET ROWCOUNT 0 afterwards, or else all other
queries you execute will be limited to @pageSize rows of output.
Note 2: Don't leave out the order by clause, or else your output will
be unpredictable. Result sets, like tables, are unordered by default.
If you get the first 10 from an unordered collection, there's no way
of predicting which 10 it will be, nor can anybody guarantee that
you'll get the same 10 if you get "the first 10" again.
2. Using ANSI-standard syntax:
SELECT DISTINCT routeID, routeName
FROM tblRoute_Header AS RH1
WHERE ...
AND (SELECT COUNT(*)
FROM tblRoute_Header AS RH2
WHERE RH2.routeID < RH1.routeID) < @pageSize
ORDER BY routeID
Note 1: This is based on assumptions re your data structure. You need
to adapt it to your actual situation.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
