Hi,

I'm working on a SQL Server project right now, and I'm not sure how to
approach one part. Basically I have a table full of orders for a
software program to process, then mark with the date/time to show it's
been finished.

What's in the Q at any time could be a few orders, or several hundred
thousand. So I don't want to return the whole query; only the first
chunk, then when the program is done with that it can move on and grab
more.

That means SELECT TOP N, except that N needs to be a variable. When
CPU and network traffic are free it should grab more rows, and when
demand is high, it should have a coffee break.

I've tried:

Create Procedure vwAutoQ
@GrabRowCount Int = 100
As

Select Top @GrabRowCount
[...]

From
[...]

Where
[...]

Order By
[...]

And I get the following error:


Server: Msg 170, Level 15, State 1, Procedure vwAutoQ, Line 5
Line 5: Incorrect syntax near '@GrabRowCount'.


Is this possible, what I'm trying to do? Otherwise I'll need to drop
it down to ~15 and fire the proc a bunch of times...

On 22 Nov 2004 18:17:28 -0800, Thug Passion wrote:

(snip)
>That means SELECT TOP N, except that N needs to be a variable. When
>CPU and network traffic are free it should grab more rows, and when
>demand is high, it should have a coffee break.

Hi Thug,

You can't use a variable on the TOP keyword. But there is a workaround:
use SET ROWCOUNT. This will take a variable.

SET ROWCOUNT @GrabRowCount
SELECT ....
FROM ....
WHERE ....
ORDER BY ....
SET ROWCOUNT 0

(Don't forget to set rowcount back to 0 after the query, as this is a
sticky setting: the limited rowcount remains active until you reset it or
drop the connection)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Various paging techniques discussed here:
http://www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @sSQL NVARCHAR(500)

SELECT @sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@iNoRows) + ' rest of
string ' ...

EXEC(@sSQL)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @sSQL NVARCHAR(500)

SELECT @sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@iNoRows) + ' rest of
string ' ...

EXEC(@sSQL)

> Hi Thug,

Hi!

> You can't use a variable on the TOP keyword. But there is a workaround:
> use SET ROWCOUNT. This will take a variable.

Awesome! I love it! That gets me exactly what I need, and except for
those two lines it doesn't change my SQL at all. I had no idea I
could use a variable with that type of (non-relational) command -
thanks very much!!

> DECLARE @sSQL NVARCHAR(500)

Hi,

Thanks for the response! I try to avoid this approach whenever
possible, it's gotten me in trouble in the past. I had a search
function in an SP that built a dynamic SQL command to take advantage
of indexes on whatever fields were passed in ( instead of a bunch of
like '%' statements ).

I declared a varchar(2000) to hold my command, and if I passed in
enough parameters, it came up to about 2300. But that was the last
thing I ever thought of to check...