AspNetPager分页控件存储过程

SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO

ALTERPROCEDUREusp_PagingLarge
@TableNamesVARCHAR(200),--表名，可以是多个表，但不能用别名
@PrimaryKeyVARCHAR(100),--主键，可以为空，但@Order为空时该值不能为空
@FieldsVARCHAR(200),--要取出的字段，可以是多个表的字段，可以为空，为空表示select*
@PageSizeINT,--每页记录数
@CurrentPageINT,--当前页，0表示第1页
@FilterVARCHAR(200)="",--条件，可以为空，不用填where
@GroupVARCHAR(200)="",--分组依据，可以为空，不用填groupby
@OrderVARCHAR(200)=""--排序，可以为空，为空默认按主键升序排列，不用填orderby
AS
BEGIN
DECLARE@SortColumnVARCHAR(200)
DECLARE@OperatorCHAR(2)
DECLARE@SortTableVARCHAR(200)
DECLARE@SortNameVARCHAR(200)
IF@Fields=""
SET@Fields="*"
IF@Filter=""
SET@Filter="WHERE1=1"
ELSE
SET@Filter="WHERE"+@Filter
IF@Group<>""
SET@Group="GROUPBY"+@Group

IF@Order<>""
BEGIN
DECLARE@pos1INT,@pos2INT
SET@Order=REPLACE(REPLACE(@Order,"asc","ASC"),"desc","DESC")
IFCHARINDEX("DESC",@Order)>0
IFCHARINDEX("ASC",@Order)>0
BEGIN
IFCHARINDEX("DESC",@Order)<CHARINDEX("ASC",@Order)
SET@Operator="<="
ELSE
SET@Operator=">="
END
ELSE
SET@Operator="<="
ELSE
SET@Operator=">="
SET@SortColumn=REPLACE(REPLACE(REPLACE(@Order,"ASC",""),"DESC",""),"","")
SET@pos1=CHARINDEX(",",@SortColumn)
IF@pos1>0
SET@SortColumn=SUBSTRING(@SortColumn,1,@pos1-1)
SET@pos2=CHARINDEX(".",@SortColumn)
IF@pos2>0
BEGIN
SET@SortTable=SUBSTRING(@SortColumn,1,@pos2-1)
IF@pos1>0
SET@SortName=SUBSTRING(@SortColumn,@pos2+1,@pos1-@pos2-1)
ELSE
SET@SortName=SUBSTRING(@SortColumn,@pos2+1,LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET@SortTable=@TableNames
SET@SortName=@SortColumn
END
END
ELSE
BEGIN
SET@SortColumn=@PrimaryKey
SET@SortTable=@TableNames
SET@SortName=@SortColumn
SET@Order=@SortColumn
SET@Operator=">="
END


DECLARE@typevarchar(50)
DECLARE@precint
SELECT@type=t.name,@prec=c.prec
FROMsysobjectso
JOINsyscolumnscono.id=c.id
JOINsystypestonc.xusertype=t.xusertype
WHEREo.name=@SortTableANDc.name=@SortName




IFCHARINDEX("char",@type)>0
SET@type=@type+"("+CAST(@precASvarchar)+")"


DECLARE@TopRowsINT
SET@TopRows=@PageSize*@CurrentPage+1

PRINT@type
DECLARE@sqlNVARCHAR(4000)

SET@Sql="DECLARE@SortColumnBegin"+@type+"
SETROWCOUNT"+Cast(@TopRowsasVARCHAR(10))+"SELECT@SortColumnBegin="+
@SortColumn+"FROM"+@TableNames+""+@Filter+""+@Group+"ORDERBY"+@Order+"
SETROWCOUNT"+CAST(@PageSizeASVARCHAR(10))+"
SELECT"+@Fields+"FROM"+@TableNames+""+@Filter+"AND"+@SortColumn+""+@Operator+"@SortColumnBegin"+ISNULL(@Group,"")+"ORDERBY"+@Order+""

--Print(@sql)
Exec(@sql)


END

GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO