TOP with parameter
-
Is it possible to use TOP keyword with parameter? For example, my SP has parameter @topnproduct to point how many products to return. I can use set rowcount @topnproduct. But I am just curious how to use top in the same manner? Thanks
-
try this
declare @sqlstring nvarchar(4000)
set @sqlstring = 'select top ' + cast(@topnproduct as varchar) + ' * from table'
exec(@sqlstring)
-
I know this works but as my query a bit complex I don't want to use dynamic Sql. Thanks
-
Unfortunately you cannot use top with a variable in SQL 2000. Most of the time, people either use set ROWCOUNT, dynamic SQL OR... You could insert the data into a temp table with an int identity(1,1) column. You can then select data where the identity column has a value <= @topNproduct.
-
Another great reason to upgrade to Yukon, where this is allowed.
-
Thanks, guys
-
while TOP doesn't allow a variable in SQL2000(but does in 2005) you can use rowcount instead for the same results:
drop procedure pr_search
create procedure pr_search(@limit int=0)
as
--rowcount of 0 = all,
--rowcount of 40 is obviously 40 40ws
set rowcount @limit
select name from sysobjects where xtype='U' order by name
pr_search (all tables in my db)
pr_search 12 (just 12 tables)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
and be sure that after your select statement you set rowcount back to 0 - the ROWCOUNT setting affects inserts, updates and deletes, not just select!
So if you did
set ROWCOUNT 50
select * from myTable
delete * from myTable
Only 50 rows would be deleted
Fix would be
set ROWCOUNT 50
select * from myTable
set ROWCOUNT 0
delete * from myTable
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply