Parameter to Top clause - SQL Server Forums

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

SQL Server Development (2000)

Parameter to Top clause

Author

Topic

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-05-07 : 06:23:54

Is there any way to make the top clause of select operate on a parameter ?

The way I need is

select top @MyCount * from Employee

This is done through stored procedure. I can pass @MyCount as parameter to the stored procedure. Any help on this would be on this problem would be greatly appreciated.

S.Mohamed Yousuff

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-07 : 06:33:35

Look for help on dynamic sql.

Something like this should help:
declare @MyCount varchar(2)
declare @sql nvarchar(200)
select @mycount = 6
select @sql = 'select top ' + @MyCount + ' * from Employee'
exec sp_executesql @sql

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-05-07 : 06:45:19

Thanks for the solution. I had an idea like this. But I am not sure about the performance. I think, this way of doing will recompile everytime for changing parameters. Of course, sqlserver cache may try to reuse the plan but I think it is not as good as a 'perfect' parameter to a stored procedure. Anyway, your information was useful. Thanks once again.

quote:

Look for help on dynamic sql.

Something like this should help:
declare @MyCount varchar(2)
declare @sql nvarchar(200)
select @mycount = 6
select @sql = 'select top ' + @MyCount + ' * from Employee'
exec sp_executesql @sql

macka
Posting Yak Master

162 Posts

Posted - 2002-05-07 : 08:00:26

Give this a try:

CREATE PROCEDURE spTopNRecords
@intTop INTEGER
AS
-- set how many records to return
SET ROWCOUNT @intTop

SELECT *
FROM SomeTable

-- set to default
SET ROWCOUNT 0

GO

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-05-07 : 09:20:24

Thanks macka. It works perfectly as expected. Thanks for the information. Actually I am using the top operator in the main query and in the subquery also. Just to simplify the description of my actual problem, I posted it like that.

Please let me know if it is possible to solve it in my actual problem.

quote:

Give this a try:

CREATE PROCEDURE spTopNRecords
@intTop INTEGER
AS
-- set how many records to return
SET ROWCOUNT @intTop

SELECT *
FROM SomeTable

-- set to default
SET ROWCOUNT 0

GO

macka
Posting Yak Master

162 Posts

Posted - 2002-05-07 : 10:52:52

If both the main and sub-queries use the same top figure then I don't think you'll run into any problems....can you post the full query (or psuedo query) - then I can let you know for sure.

macka.

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-05-08 : 05:35:44

The problem is related to record paging using sqlserver. I am using .NET to access sqlserver. There is no concept of connected recordset with sqlserver in .NET which means no server side cursors. For simplicity I will explain my problem through the traditional Employees table.

I have a employees table with large number of records say 500,000 records. There is no identity column in my table. While paging the records I may sort it based on any column. For a given page number and page size and sort order, only those records should be pulled from the server. I prefer not to use any dynamic sql or temp tables. Dynamic sql may solve the problem with some performance degradation but temp tables is not acceptable due to the large number of records. The query to achieve paging is like

select TOP 10 * from Employees where EmployeeId not in ( select TOP 20 EmployeeId from Employees)

In the above query 10 is the number of records to retrieve (page size) and 20 is the starting position of the record. The starting position is calculated by first issuing a recordcount query prior to issuing the above query and dividing the record count by the page size.

Just executing the query after building it dynamically solves the problem but it degrades the performance. Hence I am looking for a better solution.

S.Mohamed Yousuff

quote:

If both the main and sub-queries use the same top figure then I don't think you'll run into any problems....can you post the full query (or psuedo query) - then I can let you know for sure.

macka.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-05-08 : 06:01:45

from the description of your problem.....have a look at the article on this site called...WHAT'S AFTER TOP.....

I think your situation is covered by the solution proposed....it certainly sounds like it.

Pradip
Starting Member

32 Posts

Posted - 2009-11-20 : 01:38:02

Mohamed ,I think, this could help you.

select top (@MyCount) * from Employee

Thanks,
visit us : www.silicus.com

pradipjain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources