--分页存储过程按主键分页
create PROCEDURE HP_Sql
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0 --排序方式升序非降序
begin
set @strTmp = '<(select min' --小于前面数据最小的主键
set @strOrder = ' order by [' + @fldName +'] desc' --主键按降序排序
end
else
begin
set @strTmp = '>(select max'--大于前面数据最大的主键
set @strOrder = ' order by [' + @fldName +'] asc'--主键按升序排序
end
---拼装完整的SQL语句
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != '' --加入where 条件
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1 --第一页单独判断并直接top查询
begin
set @strTmp =''
if @strWhere != ''--加入where条件
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0 --计算总条数
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
--exec (@strSQL) --执行分页查询
print @strSQL --显示完整的SQL语句
GO
--执行分页存储过程
exec HP_Sql 'student','sno',3,3,0,0,''
--------------------------------------------------------------------
-----分页存储过程按记录条分页
create procedure p_pagesplit @pagesize int,@currentpage int --@pagesize每页条数，@currentpage当前页
as
declare @start int,@end int
declare @sql varchar(1000)---完整的sql语句
set @start=@pagesize*(@currentpage-1)+1--计算开始位置
set @end=@pagesize*@currentpage--计算结束位置
set @sql='select * from (select top '+str(@pagesize)+' * from (select top '+str(@end)+' * from student order by sno asc) temp order by sno desc) temp2 order by sno asc'
exec(@sql)--执行查询语句
-------------------------------
--触发器
--创建张表 一个test 一个testlog
create table test (
id int identity(1,1) primary key,
names varchar(20) ,
address varchar(30)
)
create table testlog(
id int identity(1,1) primary key,
username varchar(20),
opptime datetime,
oppwork varchar(30)
)
-----对表test做insert,delete,update操作的时候触发testlog表进行记录
create trigger tritest on test
for insert,delete,update
as
insert into testlog values(user,getdate(), '操作')
-------------------------------------------
select * from test;
select * from testlog;
insert into test values('wangwu','beijing');
update test set names='wangwu2' where names='wangwu';
delete from test where names='wangwu2'