MSSQL存储过程应用

 

 

1.原始表inoutinfo

2.现在想输入时间范围和操作类型输出对应的结果

2.1创建存储过程

create proc selecttype
@type nvarchar(10),@starttime datetime,@endtime datetime
as
select opdt,opemp,optype,num from inoutinfo where optype=@type and opdt between @starttime and @endtime
go

2.2执行存储过程

exec selecttype '入库','2019-01-18 23:15:25.100','2019-01-18 23:15:25.113'

3.现在想输入时间范围查询所有种类的各自操作的数量

3.1修改存储过程

alter proc selecttype
@starttime datetime,@endtime datetime
as
select optype,sum(num) from inoutinfo where opdt between @starttime and @endtime group by optype
go

3.2执行存储过程

exec selecttype '2019-01-18 23:15:25.100','2019-01-19 23:15:25.113'

4.现在想输入查询类型，时间范围输出明细或汇总

4.1修改存储过程

alter proc selecttype
@selecttype nvarchar(10),@type nvarchar(10),@starttime datetime,@endtime datetime
as
if(@selecttype='汇总')
select optype,sum(num) from inoutinfo where opdt between @starttime and @endtime group by optype
else
select opdt,opemp,optype,num from inoutinfo where optype=@type and opdt between @starttime and @endtime
go

4.2执行存储过程

exec selecttype '','消耗','2019-01-18 01:15:25.100','2019-01-19 13:20:25.113'

exec selecttype '汇总','','2019-01-18 01:15:25.100','2019-01-19 13:20:25.113'