George writes "George Drumea writes "I have a stored procedure that i'd like to be able to use to page the results of a query. The query must also be dynamically built, because the sort options change. Here's a code snippet...CREATE PROCEDURE sp_ListPersons(@Page int,@RecsPerPage int,@SortCrit varchar(64) )ASEXEC('DECLARE CrsPerson CURSOR FOR SELECT ID, Name, Department FROM Persons WHERE (ID < 9000) ORDER BY '+@SortCrit)OPEN CrsPersonSELECT @TotalRecords = @@CURSOR_ROWSThe Persons table looks like this...create table Persons(ID integer primary key, Name nvarchar(64) not null, Department nvarchar(32) not null, Picture nvarchar(255) not null,Visitor integer not null)
The problem is that when @SortCrit contains something like 'Name asc' (sort by Name), everything is ok, but when @SortCrit contains something like 'ID asc', or 'ID desc' (sort by the primary key), @@CURSOR_ROWS is -1 and i can't understand why...Apparently, when i attempt to order by a primary or unique key in a select used to construct a cursor, @@CURSOR_ROWS is -1.I use Microsoft SQL Server 7.0Any ideas?"
Cursors, Dynamic SQL and primary keys 
Posted - 2003-04-17 : 07:36:42
