Frans,

Is there a way to limit the number of records returned from a Stored Procedure using RetrievalProcedures?

Marcus

Not at the moment, no... I could add an option that the dataadapter (the ado.net one) fetches a limited amount of rows, but I can never add TOP to the actual SQL.

I guess this is because of the lack of parameterised TOP in SqlServer 2000...

Is there any advantage in limiting the number records returned from ADO.NET?

Marcus wrote:

I guess this is because of the lack of parameterised TOP in SqlServer 2000...

Well, I just call the proc, so whatever happens in the proc, I can't do a thing about it I could wrap the call with SELECT TOP x * FROM (EXEC proc... ) or something, but that's not always working if I recall correctly.

Is there any advantage in limiting the number records returned from ADO.NET?

if the proc itself limits the rows, the RDBMS can tune resources about the resultset being returned. If the resultset is 100000 rows and you want the first 10, reading the first 10 and then quit (which is what SqlDataAdapter does) is not that efficient, as SqlServer still has to reserve resources for those 100000 rows...

Frans, I recently read the following on the http://www.developers.ie forum...

I'm trying to create a stored procedure where I can send "sp_GetTopRecordSet 25" and it will return a recordset of the top 25 records of my query, like: "SELECT TOP @n * FROM MyTable ORDER BY DateColumn" Now, why won't this work? Well you've discovered a weird quirk of SQL Server: it's very picky about where it allows variables in queries. Another question posted after yours asked why you couldn't put a whole WHERE clause in a variable (Select * from table where = @whereclause). The SQL Server parser just won't let you. I'll cover both these questions here.

The easiest answer is to use the SET ROWCOUNT statement. This statement stops processing after a certain number of rows have been processed. It works for SELECT, UPDATE and INSERT. In your case the syntax would look something like this:

declare @v1 int set @v1 = 25 set rowcount @v1 select * from MyTable Order by DateColumn set rowcount 0

Always remember to use SET ROWCOUNT 0 to turn off the row limiter. You can the SQL Server Books Online for further details on this command. There really isn't much more to it though. Micrsoft suggests using the TOP command whenever possible.

So how would you use the TOP in this case? Glad you asked. Easy, just make the whole SQL statement a variable. In this case, your query is:

declare @vSQL varchar(1000), @numrows int select @numrows = 25 select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from MyTable Order by DateColumn' Execute (@vSQL)

The EXECUTE statement will run any valid SQL statement that you pass it. You can use this to dynamically generate SQL statements at run time. Keep in mind that SQL Server is providing no syntax checks of this statement until it actually runs so be very careful.

You can also use this approach to solve the problem from above with the dynamic WHERE clause. Just put your whole query into the a variable. You can build the query as you go based on the user input.

I've not seen this before and was wondering if this could be used to solve the above problem?

Marcus

Rowcount and TOP differ.

(from sql books online)

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

• The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.

• The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

So you can have different results. For example if you have a resultset with 1000 rows, and you want the first 100, and you have a sort clause applied, rowcount will not find the 110th row which should be on top of the list, TOP will.

Why did I think you might not know about this... I'll find something at some point that you don't know!

Marcus

hehe