August 15, 2009 at 12:51 pm
The stored procedure below is used in an ASP.NET application that I'm developing to effectively page through a recordset. Is it possible to return the total number of records in the recordset as well as the specific set of records being returned? If so, how would I do that? Do I have to go with a separate stored procedure for that value?
The total number of records in the underlying recordset is needed in order to correctly generate the associated Select Page [x] of [x] functionality.
[Code]
CREATE PROCEDURE [dbo].[sp_getEmployeesNotAssignedAsDelegates]
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first record or our page of records
SET ROWCOUNT @startRowIndex;
WITH RecordSet AS (SELECT Row_Number() Over (ORDER BY NameFirst, NameLast) as RowNum, ContactId FROM Contacts)
SELECT @first_id = RowNum FROM RecordSet ORDER BY RowNum
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows;
--Using Row_Number() allows the underlying record set to be sorted by something other than the primary key as in the original article
WITH RecordSet AS (SELECT ROW_NUMBER() OVER (ORDER BY NameFirst, NameLast) as RowNum, ContactId,
CASE WHEN isFacilityGeneralNumber = 1 THEN FacilityGeneralNumberDescription ELSE NameFirst + ' ' + NameLast END AS Name FROM Contacts)
SELECT RowNum, ContactId, Name FROM RecordSet WHERE RowNum >= @first_id
ORDER BY RowNum
SET ROWCOUNT 0
GO
[/Code]
If your interested the full article discussing the technique is located here.
August 15, 2009 at 1:04 pm
David ...thanks for the procedure code ...could you also please post some DDL, sample data and expected results
This will probably help you in getting quicker and tested replies
as per this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 15, 2009 at 1:51 pm
You can return an integer value from a stored procedure:
CREATE PROCEDURE usp_doSomeStuff(@param1 as int)
AS
BEGIN
SELECT SomeColumns FROM SomeTable
RETURN @@ROWCOUNT
END
EXEC @totalRows = usp_doSomeStuff
Don't know if this is what you are looking for.
Regards
Gianluca
-- Gianluca Sartori
August 15, 2009 at 3:25 pm
Looks like @@RowCount is what I was looking for thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply