TOP n (where n is a variable)??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TOP n (where n is a variable)???

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 14:42:17

Hello!

The following code doesn't work as is:


SELECT TOP @PageSize MediaInfoID, MediaID, LastUpdated
FROM MEDIAINFO AS mi1
WHERE mi1.MediaInfoID <> ALL(SELECT TOP (PageSize% * (PageNum% - 1)) MediaInfoID FROM MEDIAINFO)
AND AccountNo = 1
ORDER BY MediaID

But if I replace the variable data with actual values then it'll run. Is it possible to use a variable value with TOP? If so, what am I doing wrong?

I'd prefer to avoid using dynamic SQL if I could.

Thanks in advance!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-10 : 14:48:56

You need to use SET ROWCOUNT:

DECLARE @pagesize int
SET @pagesize=20
SET ROWCOUNT @pagesize
SELECT ...
SET ROWCOUNT 0

Make sure to include the SET ROWCOUNT 0 at the end or you could end up with short results.

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 15:01:36

Thanks rob!

I was afraid the replies would say use ROWCOUNT. I've got that in my current stored procedure but have just found out that it's not sorting my items the way they should (see [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17583[/url]). I'm trying to fix the following code:


SET @SQL = N'SET ROWCOUNT ' + CAST(@MaxNumRecords AS NVARCHAR(20))
  SET @SQL = @SQL + N' INSERT INTO #mediaTable (MediaInfoID)'
  SET @SQL = @SQL + N' SELECT MediaInfoID'
  SET @SQL = @SQL + N' FROM uvw_OffsiteMedia'
  SET @SQL = @SQL + N' WHERE AccountNo = ' + CAST(@AccountNo AS NVARCHAR(10))
  SET @SQL = @SQL + N' ORDER BY ' + CAST(@Sort AS NVARCHAR(25)) + ' ' + CAST(@SortOrder AS NVARCHAR(4))

  EXECUTE sp_executesql @SQL

I'd like to get away from the dynamic SQL in the proc. I am also hoping to possibly improve on the way I query the page of data.

Thanks again.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 07/10/2002 15:02:06

Edited by - KHeon on 07/10/2002 15:02:55

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 15:03:38

Ack, hit wrong icon, didn't mean to reply again.

Edited by - KHeon on 07/10/2002 15:04:21

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-11 : 14:41:02

Kyle,

The only other way I know to use a variable in a TOP statement is with Dynamic SQL, which you're trying to get away from...

SET @sql = 'SELECT TOP ' + @PageSize + 'FROM...'

I think those are your only two options.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources