ストアド・プロシージャ02-単純汎用ページング

create PROCEDURE [dbo].[GetPageDataOutRowNumber]
(
@tn nvarchar(30),-- 
@idn nvarchar(20),-- 
@pi int = 1,-- 
@ps  int = 7,-- 
@wh nvarchar(255) = '',--wehre 
@oby nvarchar(255) = '',--orderby  
@rc int output,-- ( )
@pc int output-- ( )
)
AS
DECLARE @sql NVARCHAR(225)='',@sqlCount NVARCHAR(225)=''
--1. 
SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@pc=CEILING((COUNT('+@idn+')+0.0)/'+ CAST(@ps AS VARCHAR)+') FROM ' + @tn
IF LEN(@wh)>1
    set @sqlCount=@sqlCount+' WHERE '+@wh
print @sqlCount
EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@pc INT OUTPUT',@rc OUTPUT,@pc OUTPUT
--2. 
--2.1 ， 
IF @pi = 1
BEGIN
    SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn
    IF LEN(@wh)>1
        set @sql=@sql+' WHERE '+@wh
    IF LEN(@oby)>1
            SET @sql=@sql+' order by ' +@oby
EXEC(@sql)
END
ELSE--2.2 ， 
BEGIN
    SET NOCOUNT ON
    SET @sql='SELECT * FROM (select row_number() over(order by '
    IF LEN(@oby)>1
        set @sql=@sql + @oby+') as rowNum,* from '+@tn
    else
        set @sql=@sql + @idn+') as rowNum,* from '+@tn
    IF LEN(@wh)>1
        set @sql=@sql+' where '+@wh
    set @sql=@sql+')as temp where rowNum>'+str(@ps * (@pi-1))+' and rowNum<='+str(@ps*@pi)
    print @sql
    EXEC(@sql)
    SET NOCOUNT OFF
END

-- 
declare @rc int,@pc int
exec [GetPageDataOutRowNumber] 'Ams_Area','ar_id',2,5,'',' ar_id desc',@rc output,@pc output
select @rc,@pc