SQL SERVER ·ÖÒ³´æ´¢¹ý³Ì

´æ´¢¹ý³Ì´úÂ룺
 --drop procedure p_page
--go
create procedure p_page
(
@Tables varchar(1000), --±íÃûÈçtesttable
@PrimaryKey varchar(100),--±íµÄÖ÷¼ü,±ØÐëΨһÐÔ
@Sort varchar(200) = NULL,--ÅÅÐò×Ö¶ÎÈçf_Name asc»òf_name desc(×¢ÒâÖ»ÄÜÓÐÒ»¸öÅÅÐò×Ö¶Î)
@CurrentPage int = 1,--µ±Ç°Ò³
@PageSize int = 10,---ÿҳ´óС
@Fields varchar(1000) = '*',--ÏÔʾµÄ×Ö¶ÎÁбí
@Filter varchar(1000) = NULL,--Ìõ¼þÓï¾ä,²»¼Ówhere,Èç f_id>3
@Group varchar(1000) = NULL,--·Ö×é×Ö¶Î
@TotalPage int output --·µ»Ø×ÜÒ³Êý
)
WITH ENCRYPTION ---¼ÓÃÜ´æ´¢
AS
SET NOCOUNT ON
Declare @intResult Int
Begin Tran
DECLARE @sql nvarchar(4000)

if @Filter is null or @Filter=''
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables
else
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter
EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--¼ÆËã×ܼǼÊý
select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--¼ÆËã×ÜÒ³Êý


IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
IF CHARINDEX('ASC', @Sort) > 0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END

ELSE
BEGIN
SET @strSortColumn = @SORT
SET @operator = '>='
END

IF CHARINDEX('.', @str