December 24, 2001 at 12:10 pm
I want to dynamically control hte number of records returned from a stored procedure. How can I achieve it? I have tried the following query but it does not work....
create procedure myproc(
@n int
) as select top @n field1,field2 from table1
i could not run the above query so had to run the query as specified below:
create procedure myproc2(
@n int
)as
BEGIN
declare @sql varchar(8000)
set @sql = 'select top ' + str(@n) + ' field1, field2 from table1'
exec(@sql)
END
Please give a better solution to achieving this.
Thankx in advance.
Paras Shah
Evision Technologies
Mumbai, India
Edited by - paras_98 on 12/24/2001 12:12:51 PM
Paras Shah
Evision Technologies
Mumbai, India
December 25, 2001 at 1:41 am
December 25, 2001 at 2:53 am
Thankx Steve... ROWCOUNT did not click me!
quote:
Use SET ROWCOUNT @nbefore the select and
SET ROWCOUNT 0 after.
Steve Jones
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
December 25, 2001 at 6:52 am
If you're returning a recordset, why not create a view and then apply the Top N to the select on the view? Not the TSQL that creates the view, the select against the view.
Andy
December 25, 2001 at 2:02 pm
How would it be beneficial by creating a VIEW and then querying on the VIEW? Wouldn't it add up to an extra step for the SQL Server to return a recordset to me. Please explain.
Thankx
quote:
If you're returning a recordset, why not create a view and then apply the Top N to the select on the view? Not the TSQL that creates the view, the select against the view.Andy
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
December 25, 2001 at 6:53 pm
They should resolve to the same thing, but the view actually will be precompiled. My method and Andy's should be about the same. This assumes that the view is created prior to the query.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply