Hi eformx,
I have created a sample for you just take a look.
--EXEC GetEmployees_Pager 'London',1,10,0
ALTER PROCEDURE GetEmployees_Pager
(
@City NVARCHAR(15)
,@PageIndex INT
,@PageSize INT
,@RecordCount INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY Employees.EmployeeId ASC
) AS RowNumber
,Employees.EmployeeId
,FirstName
,LastName
,Employees.Country
,BirthDate
INTO #Results
FROM [Northwind].[dbo].[Employees] Employees
INNER JOIN [Northwind].[dbo].[Orders] Orders
ON Employees.EmployeeId = Orders.EmployeeId
INNER JOIN [Northwind].[dbo].[Customers] Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.City = @City
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
