Question

我需要一种想法,将以下“如果是”条件转变为原始声明。 (无动态)

Dim strVar1 as String, strVar2 as String, strVar3 as String, intSet Int32

.Append("SELECT * FROM TABLE_A as A ")
.Append("INNER JOIN TABLE_B as B ON A.Code=B.Code_A ")

If strVar1.Length > 0 Then
  .Append("LEFT JOIN TABLE_C as C ON C.Code=A.Code_C ")
  .Append("LEFT OUTER JOIN TABLE_D as D on C.Code=D.Code_C AND D.Col1 IN (1,NULL) ")
End If

If strVar2.Length > 0 Or strVar3.Length > 0 Then
  .Append("LEFT OUTER JOIN TABLE_E as E ON A.Code=E.Code_A ")
  .Append("LEFT OUTER JOIN TABLE_F as F ON E.Col1=F.Col1 ")
  .Append("INNER JOIN TABLE_G ON as G ON F.Code=G.Code_F AND G.Col1=1 ")
End If

Select Case intType
    Case 1 , 2    
       If intSet <> -1 Then
         .Append("LEFT OUTER JOIN TABLE_H H on A.code=H.code_A AND H.code_H=1 AND H.Pos=1 ")
       Else
         .Append("LEFT OUTER JOIN TABLE_H H on A.code=H.code_A AND H.Pos=1 ")
       End If
    Case 3 , 4
End Select

Answer 1

我不敢肯定,将你的生动 code法变成一个具有参数的单一q。我感觉到,唯一的解决办法是,为你在检测中使用的每一种价值观组合制造一个疑问。有许多问题:如果我正确计算,有16个问题。

如果你的目标是加快你的问答,我很相信这将有助于:执行计划每次都要计算。但是,这是以更为复杂的法典为代价的。

Answer 2

You may try WHEN THEN SQL constructs! Logic: All joins will be there. However if you want to join based on some condition, put cross table column in join clause else the same table column Something like:

 SELECT DISTINCT TABLE_A.* FROM TABLE_A as A
INNER JOIN TABLE_B as B ON A.Code=B.Code_A
LEFT JOIN TABLE_C as C ON C.Code=
    CASE WHEN LEN(@strVar1)>0
    THEN
    A.Code_C -- join with your table
    ELSE
    C.Code -- join with itself to do nothing
    END
LEFT OUTER JOIN TABLE_D as D on C.Code=
    CASE WHEN LEN(@strVar1)>0 THEN
    D.Code_C AND D.Col1 IN (1,null)
    ELSE
    C.Code
    END

and so on

友情链接