多字段排序分页(存储过程)

CREATE PROCEDURE [dbo].[Pager_orderid] ( @sTable nvarchar(100), --表名@sPkey nvarchar(50),            --主键(一定要有) @sField nvarchar(1000)='*',     --字段@iPageCurr int,                 --当前页数@iPageSize int,                 --每页记录数@sCondition nvarchar(1000),  --条件(不需要where) @sOrder nvarchar(100) ,         --排序(不需要order by,需要asc和desc字符) @Counts int=0 output,           --记录条数(已有值:外部赋值,0执行count) @pageCount int=1 output     --查询结果分页后的总页数) AS SET NOCOUNT ON DECLARE @Recordcount varchar(5000) --总记录数DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000) DECLARE @iAsc int,@iDesc int,@iType tinyint DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000) /*----------------------判断where 条件是否空值-------------------*/ IF LEN(@sCondition)>2  begin SELECT @sC1=' where '+@sCondition+' ', @sC2=' where '+@sCondition+' and '  set @Recordcount= 'select count(1) as Total from[' + @sTable + '] where ' + @sCondition end ELSE  BEGIN   SELECT @sC1=' ', @sC2=' where '  END SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder=' ', @sT4=UPPER(@sPkey) /*-----------------------获取查询的数据行数---------------------*/ IF LEN(@sT2)>2 BEGIN  SELECT @iAsc=0, @iDesc=0  IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建     BEGIN   SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1)  END  IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))   SELECT @iType=1, @sT3='>(SELECT MAX('  ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))   BEGIN   SELECT @iType=1, @sT3='<(SELECT MIN(' END  SET @sOrder='ORDER BY '+@sT2 END /*-------------------------获取查询的数据行数----------------------*/ --IF (@Counts<1) --BEGIN  --SET @sSQL=’SELECT @Counts=Count(0) FROM ’+@sTable+@sC1  --EXEC sp_executesql @sSQL,N’@Counts int OUT’,@Counts OUT  --END SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT SET @pageCount=(@Counts+@ipageSize-1)/@ipageSize IF @iPageCurr>@pageCount  SET @iPageCurr=@pageCount SELECT @iPageCurr=(CASE WHEN @Counts<(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END) IF (@iPageCurr>1) AND (@iType=1)  SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder ELSE IF (@iPageCurr>1) AND (@iType=0)  SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder ELSE BEGIN     SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder END EXEC(@sSQL) EXEC(@Recordcount)SET NOCOUNT OFF