Limit rows returned using TableAdapter (typed DataSets)

Question

Does anyone know the best way to limit the number of rows returned when using typed TableAdapters? Options appear to be:

1. Pass "top X" as a parameter (can't do this until we're on SQLS2008
2. Issue a dynamic SQL statement: set rowcount X before calling my tableadapter method, then set rowcount 0 aftwards.
3. Something else I haven't thought of :).

Many thanks in advance.

Answer 1

I achieve this using parameterized stored procedure.

create procedure dbo.spFoo 
    @NoOfRows int = 200

AS


declare @sql varchar(2000)
select @sql =
'select top ' + Cast(@NoOfRows as varchar) + ' * FROM   Foo'

exec(@sql)

Answer 2

I have found a better way.

You can't parameterise the "TOP" value, but you can parameterise the "SET ROWCOUNT X" value.

Therefore, this works:

create procedure test_SelectTopFromTable (@rowCount int) as
begin
    set rowcount @rowCount
    select * from table
    set rowcount 0
end

exec test_SelectTopFromTable 100

However I do need to deal with exceptions happening, which would prevent the set rowcount 0 statement from running. I'll keep digging.

UPDATE
My boss told me the way to make it work (and it's so simple I'm a bit embarrassed) but I'm going to get him to post his answer rather than make it look like I found it myself. :)

Answer 3

You can use TOP n, but you have to put brackets around it like so:

SELECT TOP (100) * FROM ...

You can also parameterise it as follows:

DECLARE @count INT
SET @count = 100
SELECT TOP (@count) * FROM field_company