当前位置: 代码迷 >> Sql Server >> 关于存储过程与函数编写的有关问题
  详细解决方案

关于存储过程与函数编写的有关问题

热度:3   发布时间:2016-04-27 16:22:40.0
关于存储过程与函数编写的问题
我写了两个函数,一个存储过程。是不是能在exec中完成函数调用,或者把函数部分的功能放在一个存储过程中,麻烦大家帮我修改修改
建立存储过程:
create   procedure   agent_efyc1
@agent_code   varchar(10)
as
if   (select   datediff(mm,probation_date,
case   when   status= 'A '   then   getdate()  
else   cast(T_DATE+18000000   as   varchar(8))   end)+1  
from   ag1tab     where   probation_date   >   0   and   agent_code   =   @agent_code)   >   1
begin  
select   fac   +   spc   as   EFYC
from   prdtab_sh
where   agnt   =   @agent_code   and   txdate   between   dbo.s_date( '2007-05-21 ')   and   dbo.e_date( '2007-06-20 ')
end
else
begin  
select   fac   +   spc   as   EFYC
from   prdtab_sh
where   agnt   =   @agent_code   and   txdate   between   '2002-01-01 '   and   dbo.e_date( '2007-06-20 ')
end

执行过程:
exec   agent_efyc1   '1001910A10 '


两个关于日期的函数:
create   function   s_date   (@inputdate   datetime)   returns   datetime
begin
declare   @returndate   datetime
if   datepart(dw,   @inputdate)   -   1   =   1
set   @returndate   =   @inputdate   +   1
else   if   datepart(dw,   @inputdate)   -   1   =   0
set   @returndate   =   @inputdate   +   2
else  
set   @returndate   =   @inputdate  
return   @returndate
end


create   function   e_date   (@inputdate   datetime)   returns   datetime
begin
declare   @returndate   datetime
if   datepart(dw,   @inputdate)   -   1   =   6
set   @returndate   =   @inputdate   +   2
else   if   datepart(dw,   @inputdate)   -   1   =   0
set   @returndate   =   @inputdate   +   1
else  
set   @returndate   =   @inputdate
return   @returndate
end

现在问题是每次要在函数里把日期写好才能再创建存储过程并执行,是不是能在exec中完成函数调用,或者把函数部分的功能放在一个存储过程中,麻烦大家帮我修改一下,谢谢




------解决方案--------------------
create procedure agent_efyc1
@agent_code varchar(10),
@stime datetime,
@etime datetime
as
if (select datediff(mm,probation_date,
case when status= 'A ' then getdate()
else cast(T_DATE+18000000 as varchar(8)) end)+1
from ag1tab where probation_date > 0 and agent_code = @agent_code) > 1
begin
select fac + spc as EFYC
from prdtab_sh
where agnt = @agent_code and txdate between dbo.s_date(@stime) and dbo.e_date(@etime)
end
else
begin
select fac + spc as EFYC
from prdtab_sh
where agnt = @agent_code and txdate between '2002-01-01 ' and dbo.e_date( '@etime ')
end


exec agent_efyc1 '1001910A10 ', '2007-05-21 ', '2007-06-20 '
  相关解决方案