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.
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
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
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.
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.
Parameter to Top clause
Posted - 05/07/2002 : 06:23:54
