我写了两个函数，一个存储过程。是不是能在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 '