Search

May 3, 2007

Avoid dynamic query at some extend [SQL 2k]


select * from [northwind].[dbo].[orders]
This will probably returns 830 rows [thats default],

Now what if I want top 10 rows or to 20 rows may be more, I will create dynamic query like...
declare @statement varchar(100)
declare @iTop int
set @iTop=3
set @statement ='select top ' + convert(varchar(2),@iTop) + ' * from [northwind].[dbo].[orders]'
EXEC (@statement)
We can do as follows which don't requrie creating dynamic query.

declare @iTop int
set @iTop=3
set rowcount @iTop
select * from [northwind].[dbo].[orders]
This will display top 3 records!!

Now set rowcount to 0 to get all the records

set rowcount 0

No comments: