CREATE
PROCEDURE [dbo].[Profile_GET]
@PageSize
int=null,
@CurrentPage
int=null,
@SortExpression
nvarchar(max)=null
AS
BEGIN
SETNOCOUNTON
DECLARE @SqlString nvarchar(max)
Declare @UpperBand int
Declare @LowerBand int
SET @LowerBand =(@CurrentPage - 1)* @PageSize
SET @UpperBand =(@CurrentPage * @PageSize)+ 1
BEGIN
SET @SqlString=‘WITH tempProfile AS
(
SELECT
[ProfileId],
[Name],
[Address],
[Email],
[Mobile],
[Active] = CASE [IsActive] WHEN 1
THEN ‘‘Active‘‘ WHEN 0 THEN ‘‘DeActive‘‘ END,
ROW_NUMBER() OVER (ORDER BY ‘
+ @SortExpression +‘ ) AS RowNumber
FROM [dbo].[Profile]
)
SELECT
[ProfileId],
[Name],
[Address],
[Email],
[Mobile],
[Active]
FROM
tempProfile
WHERE
RowNumber > ‘
+CONVERT(VARCHAR,@LowerBand)+‘ AND RowNumber < ‘+CONVERT(VARCHAR, @UpperBand)
+‘ ORDER BY ‘+ @SortExpression ;
EXECsp_executesql@SqlString
END
END
;
CREATE
PROCEDURE [dbo].[Profile_Total]
AS
BEGIN
SETNOCOUNTON
SELECTCOUNT(*)FROMProfile
END
原文：http://www.cnblogs.com/happy-Chen/p/3610926.html