Stored Procedures
CREATE PROCEDURE [dbo]. [Getprofitandloss]
@agentNo varchar (10),
@o0 varchar (30),
@source varchar (30),
@beginTime varchar (19),
@endTime varchar (19)
As
DECLARE @FName varchar (2000)//long enough
BEGIN
Set @FName = ' SELECT agent_no from Agent_info INFO
where op in ("' + @op +")
and source in (' + @source + ') and agentno= ' 88888888 '//' 88888888 ' for 4 single quotes, (' ' + @source + ') for 6 single quotes
ORDER by Info.agent_no; '
EXEC (@FName)
END
GO
Call
if (Stringutils.isnotempty (Searchcriteria.getsource ())) {
Source=searchcriteria.getsource ();
}else{
Source= "Gwglobal", ' gwfx ', ' gww ';//Note The single quote section 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 (:p arm1,:p Arm2,: PARM3,:p arm4,:p ARM5)} ");
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 ();
}
});
}
Note Using agentno! in SQL Server = ' 888888 ', this sentence will filter out 888888 and null.
Custom string cutting functions.
Create function F_split (@c varchar), @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
SQL Server use Notes (ii)