sql_page

CREATE PROCEDURE  lisky_Page
(
 @iRecordCount float OUTPUT,
 @iPageCurr int,
 @iPageSize int,
 @sPkey nvarchar(50),
 @sField nvarchar(1000),
 @sTable nvarchar(100),
 @sCondition nvarchar(1000),
 @sOrder nvarchar(100)
)
AS
BEGIN
 SET NOCOUNT ON
 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)

 SELECT @sC1='', @sC2=' WHERE '
 IF LEN(@sCondition)>2
  BEGIN
   SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND '
  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 @iRecordCount<1
  BEGIN
   SET @sSQL='SELECT @iRecordCount=Count(0) FROM '+@sTable+@sC1
   EXEC sp_executesql @sSQL,N'@iRecordCount float OUT',@iRecordCount OUT
  END

 SELECT @iPageCurr=(CASE WHEN @iRecordCount<(@iPageCurr-1)*@iPageSize THEN CEILING(@iRecordCount/@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)

END
GO