如何将查询转换为存储过程?
问题描述:
如何将以下查询转换为存储过程(MS Sql Server)?
How to convert the below Query into Stored Procedure(MS Sql Server) ?
select rating,trendvalue
from rptslastats where sla='" + dsReportName.Tables[0].Rows[p][0].ToString() + "'
and to_char(asondate,'mon yy')=to_char(to_date('" + asondate.ToShortDateString() + "','mm/dd/yyyy'),'mon yy')
答
create procedure MyProc
as
select rating,trendvalue
from rptslastats
where sla='" + dsReportName.Tables[0].Rows[p][0].ToString() + "'and to_char(asondate,'mon yy')=to_char(to_date('" + asondate.ToShortDateString() + "','mm/dd/yyyy'),'mon yy')
存储过程:
stored procedure:
create procedure procedureName
(
@sla varchar(100) ,
@leftparam varchar(100),
@rightparam varchar(100)
)
as
begin
select rating,trendvalue from rptslastats
where sla= @sla and @leftparam = @rightparam
end
你必须指定值为
and you have to assign values as
@sla -> '" + dsReportName.Tables[0].Rows[p][0].ToString() + "'
@leftparam varchar(100) -> to_char(asondate,'mon yy')
@rightparam varchar(100) -> to_char(to_date('" + asondate.ToShortDateString() + "','mm/dd/yyyy'),'mon yy')
希望它工作正常......
hope it works fine...