Select Top with parameter
-
Hi, i need to select top records with variable number of records, something like this:
select top @rows from table
What is optimal way to do this? Thanks
-
Hi, try this
SET ROWCOUNT @rows
SELECT ...
SET ROWCOUNT 0 -- (Set back to default)
Thomas
-
Another way that gives you a choice on which column you want the top to be:
/* create the procedure to return top rows for a given number
hint--column_name and table_name to be replaced */
create procedure toprows
(@n smallint)
as
begin
declare @execstr nvarchar(255)
set @execstr = 'select top ' + cast(@n as nvarchar(3)) + ' column_name from table_name'
exec(@execstr)
end
/* executing script
hint--n is the number of rows returned */
exec toprows n
------------------------------
The Users are always right - when I'm not wrong! -
Thanks, I'll use for ROWCOUNT because of too compicatied SP text
-
While dynamic sql is handy sometimes, it should be used ONLY when there is NO other solution.
With dynamic sql, the RDBMS cannot preformulate the query tree, and has to do it each time. This is like passing in sql from an ADO command object or something, the RDBMS has no idea (beforehand) what you're doing, so it has to figure out the best way to run the query each time you run on.
The ROWCOUNT solution is a better one, because it doesn't rely on dynamic sql.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply