企业级SQL分页代码

 SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_SelectMercSendMail]

(

@IsApproved varchar(10),

@IsLockedOut varchar(10),

@UserName nvarchar(256),

@Email nvarchar(256),

@CreateDateTimeFrom datetime,

@CreateDateTimeTo datetime,

@iCurrentPage int,

@iPageSize int,

@vchrSortField VARCHAR(50),

@vchrSortType VARCHAR(50),

@iRecordCount INT OUTPUT

)

AS

BEGIN

 

DECLARE @SQLSTRING NVARCHAR(MAX)

DeCLARE @SQLWHERE NVARCHAR(MAX)

DeCLARE @SQLWHEREUSERNAME NVARCHAR(256)

 

DECLARE @iMinPage INT

DECLARE @iMaxPage INT

SET @iMinPage=(@iCurrentPage-1)*@iPageSize+1

SET @iMaxPage=@iMinPage+@iPageSize-1

 

SET @SQLWHERE=' Where 1=1 '

IF ISNULL(@IsApproved,'')<>''

BEGIN

SET @SQLWHERE=@SQLWHERE+' AND IsApproved='''+@IsApproved+''''

END

 

IF ISNULL(@IsLockedOut,'')<>''

BEGIN

SET @SQLWHERE=@SQLWHERE+' AND IsLockedOut='''+@IsLockedOut+''''

END

 

IF ISNULL(@Email,'')<>''

BEGIN

SET @SQLWHERE=@SQLWHERE+' AND Email LIKE ''%'+@Email+'%'''

END

IF ISNULL(@CreateDateTimeFrom,'')<>''

BEGIN

SET @SQLWHERE=@SQLWHERE+' AND CreateDate>='+''''+CAST(@CreateDateTimeFrom as varchar(100))+''''

IF ISNULL(@CreateDateTimeTo,'')<>''

BEGIN

SET @SQLWHERE=@SQLWHERE+' AND CreateDate<='+''''+CAST(@CreateDateTimeTo as varchar(100))+''''

END

END

 

 

SET @SQLWHEREUSERNAME=' WHERE 1=1 '

IF ISNULL(@UserName,'')<>''

BEGIN

SET @SQLWHEREUSERNAME=@SQLWHEREUSERNAME+' AND UserName LIKE ''%'+@UserName+'%'''

END

DECLARE @SQLSTRING1 AS NVARCHAR(MAX)

DECLARE @PARAMString NVARCHAR(MAX)

SET @SQLSTRING1='SELECT @TotalCount=COUNT(*) FROM [8SMembership].dbo.aspnet_Membership AS A

'+' Join ('+'SELECT UserName,UserID FROM [8SMembership].dbo.aspnet_Users'

+@SQLWHEREUSERNAME+')'+' AS C on A.UserID=C.UserID'+@SQLWHERE

SET @PARAMString='@TotalCount int output'

 

SET @SQLSTRING='SELECT * FROM ('

+'SELECT A.UserId,A.Email,C.UserName,ROW_NUMBER() OVER(ORDER BY '+@vchrSortField+' '+@vchrSortType+') AS RowNumber '

+' FROM [8SMembership].dbo.aspnet_Membership AS A'

+' Join ('+'SELECT UserName,UserID FROM [8SMembership].dbo.aspnet_Users'

+@SQLWHEREUSERNAME+')'+' AS C on A.UserID=C.UserID'+@SQLWHERE

+') AS B

WHERE  RowNumber BETWEEN '+CAST(@iMinPage AS VARCHAR(100))+' AND '+CAST(@iMaxPage AS VARCHAR(100))

SET @SQLSTRING=@SQLSTRING

EXEC sp_executesql @SQLSTRING

    print @SQLSTRING

exec sp_executesql @SQLSTRING1,@PARAMString,@TotalCount=@iRecordCount output

END