比如一个存储过程
CREATE PROCEDURE UP_tboldreceiverequest_GetLists
@operateaccount char(16),
@gdcode varchar(16),
@stockcode varchar(12),
@contractcode varchar(32),
@beginTime datetime,
@endTime datetime
AS
begin transaction
...........
条件组合比如"@operateaccount is not null“+"@gdcode is null"或者"@operateaccount is not null“+"@beginTime is not null"+"@endTime is not null"等等所有你能够想得到的条件组合(@beginTime与@endTime限制时间段的),我知道用if.....else if......else if.......可以实现上述分类模糊查询,但是太麻烦了,总是在不停的判断。请朋友们看下,能否提供比较实用的实现方法
CREATE PROCEDURE UP_tboldreceiverequest_GetLists
@operateaccount char(16),
@gdcode varchar(16),
@stockcode varchar(12),
@contractcode varchar(32),
@beginTime datetime,
@endTime datetime
AS
begin transaction
...........
条件组合比如"@operateaccount is not null“+"@gdcode is null"或者"@operateaccount is not null“+"@beginTime is not null"+"@endTime is not null"等等所有你能够想得到的条件组合(@beginTime与@endTime限制时间段的),我知道用if.....else if......else if.......可以实现上述分类模糊查询,但是太麻烦了,总是在不停的判断。请朋友们看下,能否提供比较实用的实现方法
然后针对每个条件写
if @xx is not null
@sql=@sql+' and xx='+@xx
@operateaccount char(16),
@gdcode varchar(16),
@stockcode varchar(12),
@contractcode varchar(32),
@beginTime datetime,
@endTime datetime
AS select * from t
where (case when @operateaccount is null then '' else operateaccount end) like '%'+isnull(@operateaccount,'')+'%'
and (case when @gdcode is null then '' else gdcode end) like '%'+isnull(@gdcode ,'')+'%'
and (case when @stockcode null then '' else stockcode end) like '%'+isnull(@stockcode ,'')+'%'
and (case when @contractcode is null then '' else contractcode end) like '%'+isnull(@contractcode ,'')+'%'
and date between @beginTime and @endTime
CREATE PROCEDURE UP_tbaccountcashmoverecord_GetList
@accountcode varchar(16),
@operateaccount varchar(16),
@beginTime datetime,
@endTime datetime
AS
SELECT
[yybcode],[accountcode],[operateaccount],[useraccount],[ettype],[bsflag]=case bsflag
when '0' then '买'
when '1' then '卖'
end,
[currencysign],[gdcode],[runamount],[afteramount],[stockcode],[stockname],
[madevolume],[madeprice],[stockamount],[contractcode],[transdate],[madetime],
[handcharge],[stampduty],[tradefee],[adminfee],[transfee],[re],
[movetype],[inputtime],[reserved1],[reserved2],[reserved3],[reserved4],[reserved5]
FROM tbaccountcashmoverecord
where(case when @accountcode is null then '' else operateaccount end) like '%'+isnull(@accountcode,'')+'%'
and (case when @operateaccount is null then '' else operateaccount end) like '%'+isnull(@operateaccount,'')+'%'
and inputtime between @beginTime and @endTime
where @whereString