CREATE PROCEDURE? Zkx_Ibcom_DevPage
?
????? @PageSize int,?? --每頁的記錄條數?
????? @PageNumber int--當前頁面
???????????
???
?
?AS
?? /*
SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 頁大小*頁數 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
?*/
??? Declare @SqlTable AS varchar(1000)? --數據表
??? Declare @SqlFiled AS Varchar(1000)?? -- 數據字段
??? Declare? @SqlWhere AS Varchar(1000) --數據查詢條件
??? Declare? @SqlText?? AS Varchar(1000)??? --SQL字符串
?IF @PageNumber >1
? BEGIN
?
? SET? @SqlTable=' test '
? SET?? @SqlFiled='*'
? SET?? @SqlWhere= ' (ID < (SELECT MIN(id) FROM (SELECT TOP?? '+CAST((@PageNumber-1)*@PageSize AS varchar(30)) + '? id? FROM '+@SqlTable+' ORDER BY id desc) AS T))'
?
? SET? @SqlText='SELECT TOP? '+CAST(@PageSize AS varchar(30)) +? ' '+@SqlFiled+' FROM ' + @SqlTable +' where '+@SqlWhere?? +'ORDER BY ID DESC'
??? Exec(@SqlText)
? END
ELSE
?? BEGIN
????? SET @SqlText=? 'SELECT TOP '+? CAST(@PageSize AS varchar(30))+ '? * FROM TEST ORDER BY ID DESC '
????? Exec(@SqlText)
?? END
GO