SQL JOIN 仅当条件为真时

Question

我的 SQL 存储过程中有 3 个变量，并且仅当第 3 个变量不为空时才想添加一个连接。

这就是我尝试做的方式，但它不起作用。它在指出的行上给出了以下错误:

Incorrect syntax near '{'

ALTER PROCEDURE [dbo].[Search] 
@one NVARCHAR(50), @two NVARCHAR(50), @three NVARCHAR(50)

SELECT  cinfo.ID,
    cinfo.Nam,
    cinfo.INAM,
    cinfo.CA,
    cinfo.Form,
    cinfo.Std,
    cval.Prop,
    cval.Cons,
    sc.Accep

From dbo.Info AS cinfo
Inner JOIN dbo.values AS cval
    ON cinfo.ID = cval.ID
INNER JOIN dbo.Sources AS sc
    ON (cval.sID = sc.sID AND sc.Accept = 'A')
IF @three IS NOT NULL{               **<---------------------**
LEFT JOIN dbo.Synonym AS synm
    ON cinfo.ID = synm.ID}

where (cinfo.NAM LIKE '%'+@one+'%' OR cinfo.CAS LIKE '%'+@two+'%' OR 
  synm.SynonymID LIKE '%'+@three+'%') AND
    (cval.PropID = '1' OR
    cval.PropID = '2' OR
    cval.PropID = '3' OR
    cval.PropID = '4' OR)

Answer 1

你不能只在连接上使用 和 吗？将尝试连接，但如果@three 为空，则不会发生同义词连接……或者出于性能原因，您真的需要忽略连接吗？如果是这样的话，动态 SQL 是我能看到让它工作的唯一方法......

ALTER PROCEDURE [dbo].[Search] 
@one NVARCHAR(50), @two NVARCHAR(50), @three NVARCHAR(50)

SELECT  cinfo.ID,
    cinfo.Nam,
    cinfo.INAM,
    cinfo.CA,
    cinfo.Form,
    cinfo.Std,
    cval.Prop,
    cval.Cons,
    sc.Accep

From dbo.Info AS cinfo
Inner JOIN dbo.values AS cval
    ON cinfo.ID = cval.ID
INNER JOIN dbo.Sources AS sc
    ON (cval.sID = sc.sID AND sc.Accept = 'A')
LEFT JOIN dbo.Synonym AS synm
    ON cinfo.ID = synm.ID
   and @three IS NOT NULL               **<---------------------**

where (cinfo.NAM LIKE '%'+@one+'%' OR cinfo.CAS LIKE '%'+@two+'%' OR 
  synm.SynonymID LIKE '%'+@three+'%') AND
    (cval.PropID = '1' OR
    cval.PropID = '2' OR
    cval.PropID = '3' OR
    cval.PropID = '4' OR)