print 'procedure tb_sp_role...'
go
if exists(select name from sysobjects where name = 'tb_sp_role' and type = 'P')
drop procedure tb_sp_role
go
create procedure tb_sp_role
(
@error_info varchar(255) output,
@head_operid varchar(20), -- 当前用户ID
@head_companyID varchar(20), -- 当前公司ID
@head_version varchar(20), -- 功能版本
@head_action int, -- 操作类别
@pageIndex int, -- 分页索引
@pageSize int, -- 分页记录数
@role_id int,
@role_pid varchar(20),
@role_code varchar(50),
@role_name varchar(100),
@manager_id varchar(20),
@role_desc varchar(255),
@belongTo varchar(30),
@reserve1 int,
@reserve2 varchar(30),
@reserve3 float
)
with encryption
as
declare
@error_no int-- 空值保护
select
@error_info = isnull(@error_info, ''),
@head_operid = isnull(@head_operid, ''),
@head_companyID = isnull(@head_companyID, ''),
@head_version = isnull(@head_version, ''),
@head_action = isnull(@head_action, 0),
@pageIndex = isnull(@pageIndex, 0),
@pageSize = isnull(@pageSize, 0),
@role_id = isnull(@role_id,0),
@role_pid = isnull(@role_pid,''),
@role_code = isnull(@role_code,''),
@role_name = isnull(@role_name,''),
@manager_id = isnull(@manager_id,''),
@role_desc = isnull(@role_desc,''),
@belongTo = isnull(@belongTo,''),
@reserve1 = isnull(@reserve1,0),
@reserve2 = isnull(@reserve2,''),
@reserve3 = isnull(@reserve3,0)-- 1: 查询 2:分页查询
if not @head_action in (1,5)
begin
select @error_no = 1, @error_info = '[tb_sp_role]操作类型参数不正确!'
select @error_no as error_no, @error_info as error_info
return @error_no
endif @head_action = 1 -- 查询
begin
select * from td_busOnLine.dbo.sys_role
where(role_id = @role_id or @role_id=0) and (role_pid = @role_pid or @role_pid = '' ) and (role_code = @role_code or @role_code = '' ) and (role_name = @role_name or @role_name = '' ) and (manager_id = @manager_id or @manager_id = '' ) and (role_desc = @role_desc or @role_desc = '' ) and (belongTo = @belongTo or @belongTo = '' ) and (reserve1 = @reserve1 or @reserve1=0) and (reserve2 = @reserve2 or @reserve2 = '' ) and (reserve3 = @reserve3 or @reserve3=0)
end
if @head_action = 5 --分页查询(不写成函数是因为要用到跟其他表的左右链接)
begin
declare @tablename varchar(20) -- 主语句
declare @RunStr varchar(5000) -- 主语句
declare @idxName varchar(255) -- 主键字段名
declare @rsCount varchar(20) -- 返回记录数
declare @sqlWhere varchar(1500) -- 查询条件 (注意: 要加 where)
/* 这里增加查询条件? */
set @tablename= 'sys_role' -- 表名
set @idxName='id' -- 分页条件字段
set @sqlWhere=' where 1=1' -- 当没有条件时用 where 1=1
select @rsCount=count(*) from sys_role where 1=1 --请自己增加查询条件 以@sqlWhere一致
/*=========================里面的内容不用修改==============================*/
if(@PageIndex<2)--if(@PageIndex=1)
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+ ' order by '+@idxName+' desc '
end
else
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+
' and '+@idxName+' < ( select min ('+@idxName+') from( '+
' select top '+cast(@Pagesize*(@PageIndex - 1) as varchar(20)) +' '+ @idxName+' from '+@tablename+' '+ @sqlWhere +''+
' order by '+@idxName+' desc) as T '+
' ) order by '+@idxName+' desc '
end
/*==========================================================*/
/* 增加左右链接*/
set @RunStr =' select A.* from ('+@RunStr+') A '
exec(@RunStr)
end
return 0go
go
if exists(select name from sysobjects where name = 'tb_sp_role' and type = 'P')
drop procedure tb_sp_role
go
create procedure tb_sp_role
(
@error_info varchar(255) output,
@head_operid varchar(20), -- 当前用户ID
@head_companyID varchar(20), -- 当前公司ID
@head_version varchar(20), -- 功能版本
@head_action int, -- 操作类别
@pageIndex int, -- 分页索引
@pageSize int, -- 分页记录数
@role_id int,
@role_pid varchar(20),
@role_code varchar(50),
@role_name varchar(100),
@manager_id varchar(20),
@role_desc varchar(255),
@belongTo varchar(30),
@reserve1 int,
@reserve2 varchar(30),
@reserve3 float
)
with encryption
as
declare
@error_no int-- 空值保护
select
@error_info = isnull(@error_info, ''),
@head_operid = isnull(@head_operid, ''),
@head_companyID = isnull(@head_companyID, ''),
@head_version = isnull(@head_version, ''),
@head_action = isnull(@head_action, 0),
@pageIndex = isnull(@pageIndex, 0),
@pageSize = isnull(@pageSize, 0),
@role_id = isnull(@role_id,0),
@role_pid = isnull(@role_pid,''),
@role_code = isnull(@role_code,''),
@role_name = isnull(@role_name,''),
@manager_id = isnull(@manager_id,''),
@role_desc = isnull(@role_desc,''),
@belongTo = isnull(@belongTo,''),
@reserve1 = isnull(@reserve1,0),
@reserve2 = isnull(@reserve2,''),
@reserve3 = isnull(@reserve3,0)-- 1: 查询 2:分页查询
if not @head_action in (1,5)
begin
select @error_no = 1, @error_info = '[tb_sp_role]操作类型参数不正确!'
select @error_no as error_no, @error_info as error_info
return @error_no
endif @head_action = 1 -- 查询
begin
select * from td_busOnLine.dbo.sys_role
where(role_id = @role_id or @role_id=0) and (role_pid = @role_pid or @role_pid = '' ) and (role_code = @role_code or @role_code = '' ) and (role_name = @role_name or @role_name = '' ) and (manager_id = @manager_id or @manager_id = '' ) and (role_desc = @role_desc or @role_desc = '' ) and (belongTo = @belongTo or @belongTo = '' ) and (reserve1 = @reserve1 or @reserve1=0) and (reserve2 = @reserve2 or @reserve2 = '' ) and (reserve3 = @reserve3 or @reserve3=0)
end
if @head_action = 5 --分页查询(不写成函数是因为要用到跟其他表的左右链接)
begin
declare @tablename varchar(20) -- 主语句
declare @RunStr varchar(5000) -- 主语句
declare @idxName varchar(255) -- 主键字段名
declare @rsCount varchar(20) -- 返回记录数
declare @sqlWhere varchar(1500) -- 查询条件 (注意: 要加 where)
/* 这里增加查询条件? */
set @tablename= 'sys_role' -- 表名
set @idxName='id' -- 分页条件字段
set @sqlWhere=' where 1=1' -- 当没有条件时用 where 1=1
select @rsCount=count(*) from sys_role where 1=1 --请自己增加查询条件 以@sqlWhere一致
/*=========================里面的内容不用修改==============================*/
if(@PageIndex<2)--if(@PageIndex=1)
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+ ' order by '+@idxName+' desc '
end
else
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+
' and '+@idxName+' < ( select min ('+@idxName+') from( '+
' select top '+cast(@Pagesize*(@PageIndex - 1) as varchar(20)) +' '+ @idxName+' from '+@tablename+' '+ @sqlWhere +''+
' order by '+@idxName+' desc) as T '+
' ) order by '+@idxName+' desc '
end
/*==========================================================*/
/* 增加左右链接*/
set @RunStr =' select A.* from ('+@RunStr+') A '
exec(@RunStr)
end
return 0go
解决方案 »
- 为什么生成出来是5.18
- 添加数据库后出现的编译错去
- 如何获取数据库的实际大小
- 局域网内无法互相访问数据库,急啊
- Ooracle 3个字段合并后查询
- sql数据库里被置入JS代码。有什么防止的办法吗?
- 如何在企业管理器中新建注册,原来要注册的服务器是用windows身份验证,怎么可以注册,或怎么用sa可以注册啊,谢谢
- 更改数据时出现对话框:查询设计器遇到查询错误。
- 多个数据库同时查询统计
- 有问题
- 【求助】如何使用C#将多个DataTable中的数据信息读入到同一个Excel的不同sheet中,并重命名sheet表名?
- 100分在线等个sql语句,求各位大神进来各显神通!!!!!!!!!!!!!!!!!!!
具体这表做什么的,就不清楚了...
if(@PageIndex<2)--if(@PageIndex=1)
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+ ' order by '+@idxName+' desc '
end
else
begin
set @RunStr = ' select top '+cast(@PageSize as varchar(20))+' *,' +@rsCount+' as rsCount from '+@tablename + ' ' +@sqlWhere+
' and '+@idxName+' < ( select min ('+@idxName+') from( '+
' select top '+cast(@Pagesize*(@PageIndex - 1) as varchar(20)) +' '+ @idxName+' from '+@tablename+' '+ @sqlWhere +''+
' order by '+@idxName+' desc) as T '+
' ) order by '+@idxName+' desc '
end
/*==========================================================*/
/* 增加左右链接*/
set @RunStr =' select A.* from ('+@RunStr+') A '
exec(@RunStr)
end
return 0go
是这一段,我一直搞不懂有什么用,不知道怎么调用它