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_Header
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 MorganOn 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_Header
>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)|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> 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_Header
> 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
TOP doesn't allow parameters, but SET ROWCOUNT does:
SET ROWCOUNT @.n
SELECT ...
SET ROWCOUNT 0
Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.
Simon|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> 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_Header
> 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
TOP doesn't allow parameters, but SET ROWCOUNT does:
SET ROWCOUNT @.n
SELECT ...
SET ROWCOUNT 0
Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.
Simon|||Thank you Simon for your help - appreciated,
Best wishes, John Morgan
On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:
>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> 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_Header
>>
>> 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
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon|||Thank you Simon for your help - appreciated,
Best wishes, John Morgan
On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:
>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> 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_Header
>>
>> 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
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon
No comments:
Post a Comment