Genesis Hybrid Smart Client Framework part III

��USE [BlueMarble.Genesis.Dev] /****** Object:  StoredProcedure [dbo].[Security_ApplicationRole_GetPaged]    Script Date: 05/20/2009 22:36:57 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

/*

----------------------------------------------------------------------------------------------------

-- Date Created: 20 May 2007



-- Created By: Eliyahu Business Systems (http://www.eliyahu.co.za)

-- Purpose: Gets records from the Security_ApplicationRole table passing page index and page count parameters

----------------------------------------------------------------------------------------------------

*/





CREATE PROCEDURE [dbo].[Security_ApplicationRole_GetPaged]

(



	@WhereClause varchar (2000)  ,



	@OrderBy varchar (2000)  ,



	@PageIndex int   ,



	@PageSize int   

)

AS





				

				BEGIN

				DECLARE @PageLowerBound int

				DECLARE @PageUpperBound int

				

				-- Set the page bounds

				SET @PageLowerBound = @PageSize * @PageIndex

				SET @PageUpperBound = @PageLowerBound + @PageSize



				IF (@OrderBy is null or LEN(@OrderBy) < 1)

				BEGIN

					-- default order by to first column

					SET @OrderBy = '[ApplicationGuid]'

				END



				-- SQL Server 2005 Paging

				declare @SQL as nvarchar(4000)

				SET @SQL = 'WITH PageIndex AS ('

				SET @SQL = @SQL + ' SELECT'

				IF @PageSize > 0

				BEGIN

					SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)

				END

				SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'

				SET @SQL = @SQL + ', [ApplicationGuid]'

				SET @SQL = @SQL + ', [RoleGuid]'

				SET @SQL = @SQL + ' FROM dbo.[Security_ApplicationRole]'

				IF LEN(@WhereClause) > 0

				BEGIN

					SET @SQL = @SQL + ' WHERE ' + @WhereClause

				END

				SET @SQL = @SQL + ' ) SELECT'

				SET @SQL = @SQL + ' [ApplicationGuid],'

				SET @SQL = @SQL + ' [RoleGuid]'

				SET @SQL = @SQL + ' FROM PageIndex'

				SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)

				IF @PageSize > 0

				BEGIN

					SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)

				END

				exec sp_executesql @SQL

				

				-- get row count

				SET @SQL = 'SELECT COUNT(*) as TotalRowCount'

				SET @SQL = @SQL + ' FROM dbo.[Security_ApplicationRole]'

				IF LEN(@WhereClause) > 0

				BEGIN

					SET @SQL = @SQL + ' WHERE ' + @WhereClause

				END

				exec sp_executesql @SQL

			

				END

GO