CREATE PROCEDURE [dbo].[usp_GetPagedResult_ByTbName]
(
@TableName NVARCHAR(100),
@FiledName NVARCHAR(100),
@PageIndex INT,
@PageSize INT
)
AS
DECLARE @BeginRow INT,@EndRow INT,@SQL NVARCHAR(1000);
SET @BeginRow=0;
SET @EndRow=0;
SET @BeginRow=(@PageIndex-1)*@PageSize+1;
SET @EndRow=@PageIndex*@PageSize;
IF(Len(ISNULL(@TableName,''))>0)
BEGIN
IF (LEN(ISNULL(@FiledName,''))>0)
BEGIN
SET @SQL=ISNULL(@SQL,'');
SET @SQL=@SQL+'WITH TMP AS ( SELECT *,ROW_NUMBER() OVER( ORDER BY '+@FiledName+') AS ROWNUM FROM '+@TableName+') ';
SET @SQL=@SQL+' SELECT * FROM TMP WHERE ROWNUM BETWEEN '+CAST(@BeginRow AS NVARCHAR)+' AND '+ CAST(@EndRow AS NVARCHAR);
PRINT @SQL;
EXEC (@SQL);
END
END
-----找了一个上午可以使用的简单存储过程;
来自:http://www.cnblogs.com/coolronson/archive/2009/11/25/1610506.html
THANKS
(
@TableName NVARCHAR(100),
@FiledName NVARCHAR(100),
@PageIndex INT,
@PageSize INT
)
AS
DECLARE @BeginRow INT,@EndRow INT,@SQL NVARCHAR(1000);
SET @BeginRow=0;
SET @EndRow=0;
SET @BeginRow=(@PageIndex-1)*@PageSize+1;
SET @EndRow=@PageIndex*@PageSize;
IF(Len(ISNULL(@TableName,''))>0)
BEGIN
IF (LEN(ISNULL(@FiledName,''))>0)
BEGIN
SET @SQL=ISNULL(@SQL,'');
SET @SQL=@SQL+'WITH TMP AS ( SELECT *,ROW_NUMBER() OVER( ORDER BY '+@FiledName+') AS ROWNUM FROM '+@TableName+') ';
SET @SQL=@SQL+' SELECT * FROM TMP WHERE ROWNUM BETWEEN '+CAST(@BeginRow AS NVARCHAR)+' AND '+ CAST(@EndRow AS NVARCHAR);
PRINT @SQL;
EXEC (@SQL);
END
END
-----找了一个上午可以使用的简单存储过程;
来自:http://www.cnblogs.com/coolronson/archive/2009/11/25/1610506.html
THANKS