USE cahooting
DECLARE @First_id int, @StartRow int
DECLARE @PageNo int, @maximumRows int

DECLaRE @ItemName varchar(128)
DECLARE @Region int , @AddedOn datetime 
DECLARE @OField varchar(15), @Key bigint

SET @OField = 'ItemName'

SET @PageNo=5
SET @maximumRows=50
Set @PageNo = (@PageNo * @maximumRows) - (@MaximumRows - 1)

/* Get first row from pageNo */
SET              ROWCOUNT @PageNo
                          SELECT     @Region=RegionID,@ItemName = ItemName,@AddedOn = AddedOn
                           FROM      DirectoryItems
                           ORDER BY CASE WHEN @OField = 'RegionID' THEN RegionID 
					 WHEN @OField = 'AddedOn' THEN AddedOn
				    END,
				    CASE WHEN @OField = 'ItemName' THEN ItemName
				    END

/* To Get the dynamic where we need to set to null the fields that we are not using */
IF @OField = 'RegionID'
	BEGIN
		SET @ItemName = null
		SET @AddedOn = null
	END
ELSE IF @OField = 'AddedOn'
	BEGIN
		SET @ItemName = null
		SET @Region = null
	END
ELSE IF @OField = 'ItemName'
	BEGIN
		SET @AddedOn = null
		SET @Region = null
	END

/* Actually retrieve rows */
SET              ROWCOUNT @maximumRows

                          SELECT     *
                           FROM         DirectoryItems
                           WHERE     ItemName >= COALESCE(@ItemName,ItemName) AND
				     RegionID >= COALESCE(@Region,RegionID) AND
				     AddedOn >= COALESCE(@AddedOn,AddedOn)
                           ORDER BY CASE WHEN @OField = 'RegionID' THEN RegionID 
					 WHEN @OField = 'AddedOn' THEN AddedOn
				    END,
				    CASE WHEN @OField = 'ItemName' THEN ItemName
				    END
SET              ROWCOUNT  0
GO

CREATE PROCEDURE uspGetThatPage
(
	@PageNo INT,
	@RowInPage INT,
	@OrderBy VARCHAR(100)
)
AS

SET NOCOUNT ON

-- Get the Pk in the right order
DECLARE	@Pages TABLE (PageID INT IDENTITY(0, 1), PkColumn INT)

INSERT		@Pages
		(
			PkColumn
		)
SELECT		PkColumn
FROM		cahooting..DirectoryItems
ORDER BY	CASE
			WHEN @OrderBy = 'RegionID' THEN RegionID
			WHEN @OrderBy = 'AddedOn' THEN AddedOn
			ELSE ItemName
		END

-- Now open the records for the right page
SELECT		di.*
FROM		cahooting..DirectoryItems di
INNER JOIN	@Pages p ON p.PkColumn = di.PkColumn
WHERE		p.PageID / @RowsInPage = @PageNo - 1