前几天一直在写文档,写得头都晕了
刚好,现在闲着没事,试写一个不怎么通用的存储过程,当作练习^_^
//========================================================
create procedure GetPageInfo
(
@tableName varchar(20), --表名
@keyField varchar(50), --主键
@pageIndex int, --页码
@pageSize int, --每页记录数
@selectFields varchar(100)='*', --需要返回字段名,可为空
@condition varchar(100)='', --查询条件,可无条件
@orderFieldsStr varchar(100)='' --排序字段集,可为空
)as
declare @sql varchar(1000)
declare @tmp varchar(200)
set @sql = 'select top '+cast(@pageSize as char(10))+@selectFields+' from '+@tableName
+' where '+@keyField +' not in(select top '+cast((@pageIndex-1)*@pageSize as char(10))+' '+@keyField
+' from '+@tableName
if ltrim(rtrim(@orderFieldsStr))='' and ltrim(rtrim(@condition))=''
set @tmp=')'
else if ltrim(rtrim(@orderFieldsStr))<>'' and (ltrim(rtrim(@condition)))=''
set @tmp= ' order by '+@orderFieldsStr+') order by '+@orderFieldsStr
else if ltrim(rtrim(@orderFieldsStr))='' and ltrim(rtrim(@condition))<>''
set @tmp = ' where '+@condition+') and '+@condition
else
set @tmp = ' where '+@condition+' order by '+@orderFieldsStr+') and '+@condition+' order by '+@orderFieldsStr
set @sql=@sql+@tmp
exec(@sql)
//========================================================
调用:
例如学生表Student(ID, Name, Age,Sex),学号ID为主键
1、取得学生总数,语句如下:(用于计算页码总数)
exec getpageinfo 'Student','ID',1,1,'count(*)'
2、假设每页15条数据,取第3页数据
exec getpageinfo 'Student','ID',3,15
3、假设每页15条数据,按学号从大到小,年龄从小到大取第3页女学生的(学号,姓名,年龄)信息
exec getpageinfo 'Student','ID',3,15,'ID,Name,Age','ID desc,Age'