代码之家 › 专栏 › 技术社区 › msinghm

参数仅与其他参数一起工作,但我希望它们独立工作

parameters tsql sql-server sql

msinghm · 技术社区 · 7 年前

我有以下脚本:

DECLARE @PlanId NVARCHAR(255) = ''
DECLARE @PlanName NVARCHAR(255) = ''
DECLARE @ActionID NVARCHAR(255) = ''
DECLARE @ActionDescription01 NVARCHAR(255) = ''
DECLARE @ActionDescription02 NVARCHAR(255) = ''
DECLARE @ActionDescription03 NVARCHAR(255) = ''
DECLARE @ActionDescription04 NVARCHAR(255) = ''
DECLARE @ActionDescription05 NVARCHAR(255) = ''

select ap.plan_id ,ap.plan_name ,apa.action_id ,apa.action_description
from dbo.ACTION_PLAN ap
LEFT JOIN ACTION_PLAN_LINK AS apl ON apl.PLAN_ID = ap.PLAN_ID 
LEFT JOIN dbo.ACTION_PLAN_ACTION AS apa ON apa.ACTION_ID = apl.ACTION_ID
WHERE
    (ap.PLAN_ID = @PlanId or @PlanId = '')
and (ap.PLAN_NAME = @PlanName or @PlanName = '')
and (apa.ACTION_ID = @ActionID or @ActionID = '')
and ( 
      ( (len(@ActionDescription01)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription01+ '%') ) )
   or ( (len(@ActionDescription02)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription02+ '%') ) )
   or ( (len(@ActionDescription03)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription03+ '%') ) )
   or ( (len(@ActionDescription04)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription04+ '%') ) )
   or ( (len(@ActionDescription05)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription05+ '%') ) )
    )

使用操作描述进行搜索时,上述脚本工作正常,但不允许我仅搜索计划id、计划名称或操作id。我希望能够搜索任何一个或多个参数。我还想添加在action description where子句中使用or运算符的原因,以便为搜索的每个操作带来更多结果。例如,我搜索plan\u id 100,然后在actiondescription01中输入“inform”,得到一个结果,然后在actiondescription02参数中输入“order”,得到3个结果,因为有两个结果与order相关,一个与inform相关,但它们都是一个plan\u id的一部分(如果输入了plan\u id)。

我希望这是有意义的,有人可以帮助。上面的脚本完全按照我的要求工作,但由于某些原因,我无法单独搜索PlanId、PlanName或ActionId,但如果我结合操作描述参数进行搜索,它们就会工作。

2 回复 | 直到 7 年前

paparazzo 7 年前

你有更多 ( ) 比你需要的多
这个 and ( ( 正在扼杀它。如果没有或 or 在那群人中是真的那么 and 为false。

select ap.plan_id ,ap.plan_name ,apa.action_id,  apa.action_description
from dbo.ACTION_PLAN ap
LEFT JOIN ACTION_PLAN_LINK AS apl       ON apl.PLAN_ID   = ap.PLAN_ID 
LEFT JOIN dbo.ACTION_PLAN_ACTION AS apa ON apa.ACTION_ID = apl.ACTION_ID
WHERE (ap.PLAN_ID    = @PlanId   or @PlanId = '')
  and (ap.PLAN_NAME  = @PlanName or @PlanName = '')
  and (apa.ACTION_ID = @ActionID or @ActionID = '')
  and (    ( len(@ActionDescription01) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription01 + '%' ) 
        or ( len(@ActionDescription02) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription02 + '%' ) 
        or ( len(@ActionDescription03) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription03 + '%' ) 
        or ( len(@ActionDescription04) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription04 + '%' ) 
        or ( len(@ActionDescription05) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription05 + '%') ) 
      )

修理

and (      ( len(@ActionDescription01) = 0 and len(@ActionDescription02) = 0 ...)
        or ( len(@ActionDescription01) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription01 + '%' ) 
        or ( len(@ActionDescription02) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription02 + '%' ) 
        or ( len(@ActionDescription03) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription03 + '%' ) 
        or ( len(@ActionDescription04) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription04 + '%' ) 
        or ( len(@ActionDescription05) > 1 and apa.ACTION_DESCRIPTION like '%' + @ActionDescription05 + '%') ) 
      )

Gottfried Lesigang 7 年前

我想你必须改变这些台词

or ( (len(@ActionDescription02)>1 and apa.ACTION_DESCRIPTION like ('%' + @ActionDescription02+ '%') ) )

像这样的事情

or (
    len(@ActionDescription02)=0 
    or ( len(@ActionDescription02)>0 
         and apa.ACTION_DESCRIPTION like '%' + @ActionDescription02+ '%' )
   )