存储过程
CREATE PROCEDURE [dbo].[getprofitandloss]
@agentNo varchar(10),
@o0 varchar(30),
@source varchar(30),
@beginTime varchar(19),
@endTime varchar(19)
AS
declare @FName varchar(2000) //要足够长
BEGIN
set @FName='SELECT AGENT_NO FROM AGENT_INFO info
where op in ('''+ @op +''')
and source in ('''+ @source +''') and agentNo=''88888888''//''88888888''为4个单引号，('''+ @source +''')为6个单引号
ORDER BY info.agent_no;'
exec(@FName)
END
GO
调用
if (StringUtils.isNotEmpty(searchCriteria.getSource())) {
source=searchCriteria.getSource();
}else{
source="GWGLOBAL','GWFX','GWW";//注意单引号部分GWGLOBAL','GWFX','GWW
}
public List<ProfitAndLoss> getAllProfitAndLossList(
return (List<ProfitAndLoss>) getHibernateTemplate().execute(new HibernateCallback() {
@Override
public Object doInHibernate(Session session)
throws HibernateException, SQLException {Query query = session.createSQLQuery("{CALL getprofitandloss(:parm1, :parm2, :parm3, :parm4, :parm5)}");
query.setParameter("parm1", agentNo);
query.setParameter("parm2", oo);
query.setParameter("parm3", source);
query.setParameter("parm4", DateUtil.getDateW3CFormat(beginTime));
query.setParameter("parm5", DateUtil.getDateW3CFormat(endTime));
System.out.println(query);
System.out.println(beginTime+" : "+endTime);
List arr = query.list();
}
});
}
注意在sqlserver中使用agentNo！='888888',这句会过滤掉888888和null。
自定义字符串切割函数。
create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
drop function f_split
col
--------------------
dfkd
dfdkdf
dfdkf
dffjk
