通用存储过程

--审核记录表
CREATE TABLE [AuditRecord] (
  [AId]  int identity NOT NULL,--审核Id
  [LId]  int NULL,--贷款Id
  [AState] int,--审核状态
  [ADesc] nvarchar(500),--备注
  PRIMARY KEY CLUSTERED ([AId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

--贷款申请表
CREATE TABLE [LoanApplication] (
  [LId]   int identity NOT NULL, --Id
  [LCName]  nvarchar(50),--客户名称
  [LContractNo] nvarchar(50),--合同号
  [LMoney]  float NULL,--贷款金额
  [LStartTime] datetime,--开始时间
  [LEndTime] datetime,--截止时间
  [LDayNum]  int,--总天数
  [LGuarantee] nvarchar(50),--担保方式
  [LRepayment] nvarchar(50),--结算方式
  [LState]  int,--审核状态
  PRIMARY KEY CLUSTERED ([LId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

ALTER TABLE [AuditRecord] ADD CONSTRAINT [fk_AuditRecord_LoanApplication_1] FOREIGN KEY ([LId]) REFERENCES [LoanApplication] ([LId])
GO

truncate table LoanApplication
insert into LoanApplication values('haha','FB-20200425-00002',10,'1999/09/26','1999/10/12',55,'hdffg','sds',0)
select * from LoanApplication

--查询列表存储过程
if(exists(select * from sys.objects where name='pr_SelApplication'))
drop proc pr_SelApplication
go
create proc pr_SelApplication
(
@cname  nvarchar(50),
@startTime nvarchar(80),
@endTime nvarchar(80),
@guarantee nvarchar(50),
@repayment nvarchar(50),
@state  int
)
as
begin
 --空查询条件字符串
 declare @strwhere nvarchar(100)=''
 --非空拼接字符串
 if(@cname!='' and @cname is not null)
  set @strwhere+=' and LCName like ''%'+@cname+'%'''
 if(@startTime!='' and @startTime is not null)
  set @strwhere+=' and LStartTime>='''+@startTime+''''
 if(@endTime!='' and @endTime is not null)
  set @strwhere+=' and LEndTime<='''+@endTime+''''
 if(@guarantee!='' and @guarantee is not null)
  set @strwhere+=' and LGuarantee='''+@guarantee+''''
 if(@repayment!='' and @repayment is not null)
  set @strwhere+=' and LRepayment='''+@repayment+''''
 --状态>=0拼接字符串
 if(@state>=0)
  set @strwhere+=' and LState='+convert(nvarchar(5),@state)+''
 declare @strsql nvarchar(600)=''
 set @strsql='select * from LoanApplication where 1=1 '+@strwhere+''
 exec(@strsql)
end

exec pr_SelApplication '','','','','',1

--审核申请存储过程
if(exists(select * from sys.objects where name='pr_auditApplication'))
drop proc pr_auditApplication
go
create proc pr_auditApplication
(
@lId  int,
@aDesc  nvarchar(500)
)
as
begin
 begin tran
  begin try
   --新增申请记录
   insert into AuditRecord values(@lId,1,@aDesc)
   --改变申请状态
   update LoanApplication set LState=1 where LId=@lId
   commit tran
  end try

  begin catch
   print error_message()
   rollback tran
  end catch
end

///////////////////////////////////////////////////////////****************************************************************************************************************//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////***************************************/////////////////////////////

-----分页存储过程
--判断存储过程是否存在
if OBJECT_ID('proc_Page') is not null
--删除存储过程
drop proc proc_Page
go
--创建存储过程
create  proc proc_Page
--参数
@ClaId int=0,               --班级Id
@Sage varchar(20)=null,        --出生日期
@Sname nvarchar(10)=null,   --学生名称
@SmallScore int=0,          --小成绩
@BigScore int=0,            --大成绩
@CID int=0,                 --课程Id
@Tname nvarchar(10)=null,   --讲师名字
@PageIndex int,    --当前页
@PageSize int,    --每页条数
@TotalCount int out,  --总条数
@order int=1                --1升序  0降序
as
--变量
declare
@sql varchar(max),      --总sql
@sqlWhere varchar(max), --条件
@rid int,              --分页条件
@countSql nvarchar(max)  --个数sql

set @sql='';
set @sqlWhere=' where 1=1';
set @rid=(@PageIndex-1)*@PageSize;
set @countSql='select @total=count(1)
from Classs
join Student on Classs.ClaID=Student.ClaID
join SC      on sc.SID=Student.SID
join Course  on Course.CID=sc.CID
join Teacher on Teacher.TID=Course.TID';
----条件班级Id
if @ClaId!=0
begin
set @sqlWhere+=' and Classs.ClaId='+str(@ClaId);
end
--出生日期
if @Sage is not null
begin
set @sqlWhere+=' and Sage>='''+@Sage+'''';
end
 --学生名称
if @Sname is not null
begin
set @sqlWhere+=' and Sname like ''%'+@Sname+'%''';
end
 --小成绩
if @SmallScore !=0
begin
set @sqlWhere+=' and Score>='+STR(@SmallScore);
end
 --大成绩
 if @BigScore !=0
begin
set @sqlWhere+=' and Score<='+STR(@BigScore);
end
 --课程Id
if @CID!=0
begin
set @sqlWhere+=' and CID='+str(@CID);
end
 --讲师名字
if @Tname is not null
begin
set @sqlWhere+=' and Tname like ''%'+@Tname+'%''';
end
--总条数
set @countSql+=@sqlWhere;
--执行计算条数的sql语句并且给输出参数赋值
exec sp_executesql  @countSql, N'@total int out',@total=@TotalCount out
--排序
declare @o varchar(4);
if @order=1
begin
set @o='asc';
end
if @order=0
begin
set @o='desc';
end
--总sql
set @sql='select top '+str(@PageSize)+' * from
(select * from v_student';
--条件
set @sql+=@sqlWhere;
set @sql+=' ) as temp
where rid>'+STR(@rid);
exec (@sql);