SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Parameter to Top clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/07/2002 :  06:23:54  Show Profile  Reply with Quote


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

United Kingdom
616 Posts

Posted - 05/07/2002 :  06:33:35  Show Profile  Reply with 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
Go to Top of Page

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/07/2002 :  06:45:19  Show Profile  Reply with Quote

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



Go to Top of Page

macka
Posting Yak Master

United Kingdom
162 Posts

Posted - 05/07/2002 :  08:00:26  Show Profile  Reply with 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

Go to Top of Page

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/07/2002 :  09:20:24  Show Profile  Reply with Quote


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





Go to Top of Page

macka
Posting Yak Master

United Kingdom
162 Posts

Posted - 05/07/2002 :  10:52:52  Show Profile  Reply with 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.

Go to Top of Page

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/08/2002 :  05:35:44  Show Profile  Reply with Quote


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.





Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/08/2002 :  06:01:45  Show Profile  Reply with Quote
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.

Go to Top of Page

Pradip
Starting Member

India
32 Posts

Posted - 11/20/2009 :  01:38:02  Show Profile  Reply with Quote
Mohamed ,I think, this could help you.

select top (@MyCount) * from Employee

Thanks,
visit us : www.silicus.com


pradipjain
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000