Convert WHERE logic to SQL statement

Can someone help me transform the following code to raw SQL statement?

(NOT to Dynamic SQL)

Dim blnAllow as Boolean = True

Dim intType as Int32 = 35

.Append("SELECT * FROM TABLE1 WHERE NAME='AAA' ")

Select Case intType

                    Case 35
                        .Append("AND (Type IN (2,4) OR type=8) ")
                        .Append("AND [use]=1 ")
                    Case 34
                        If blnAllow = True Then
                            .Append("AND (Type IN (2,4) OR (type=8 and Col1 > 0 )) ")
                        Else
                            .Append开发者_如何学Python("AND (Type IN (2,4)) ")
                        End If
                        .Append(" AND [use]=1 ")
                    Case Else
                        .Append("AND Type=1")
End Select

Well since intType is defined as 35, only the Case 35 section applies...

select * from TABLE1 where [NAME]='AAA'
    and [Type] in (2,4,8)
    and [use] = 1

If you want to encapsulate those other cases, you'll have to explain where intType fits in.. or do you just want 3 separate queries?

How about something like this

SELECT  * 
FROM    TABLE1 WHERE NAME='AAA'
AND     (
            (
                    intType = 35
                AND (Type IN (2,4) OR type=8)
                AND [use]=1
            )
            OR
            (
                    intType = 34
                AND (
                        (
                                blnAllow = 'true'
                            AND (Type IN (2,4) OR (type=8 and Col1 > 0 )) 
                        )
                        OR
                        (
                                blnAllow = 'false'
                            AND (Type IN (2,4)) 
                        )
                    )
                AND [use]=1
            )
            OR
            (
                    intType NOT IN (35, 34)
                AND Type=1
            )
        )

The general converting pattern is

If condition Then ands1
Else ands2 End If

becomes

( (condition AND ands1) OR ((NOT condition) AND ands2) )

Please try this most optimized query.

select * from table where name = 'AAA' AND
    (
        (
            ((Type IN (2,4) OR type=8) OR // Case 35
                (       
                    (Type IN (2,4) OR (type=8 and Col1 > 0 )) // Case 34 and blnAllow checking
                )
            )
            AND [use]=1 // Case 35 && 34
        ) OR     
        (Type=1) // Else
    )

If the string "Type" and "type" indicates the same field, You just modifify the 
//case 35 section to (Type IN (2,4) OR type=8) => (Type IN (2,4,8))

In MS SQL, this would look like this:

DECLARE @blnAllow BIT
SET @blnAllow = 1

DECLARE @intType INT
SET @intType = 35

SELECT * 
FROM TABLE1 
WHERE 
    NAME = 'AAA' AND
    (
        (@intType = 35 AND (Type IN (2,4) OR type = 8) AND [use] = 1) OR
        (@intType = 34 AND [use] = 1  AND
            (
                (@blnAllow = 1 AND (Type IN (2,4) OR (type = 8 and Col1 > 0 ))) OR
                (@blnAllow = 0 AND (Type IN (2,4)))
            )) OR
        (@intType not in (34, 35) AND Type = 1)
    )

Don't expect query optimizer to optimize it :).

…
WHERE NAME = 'AAA'
  AND (@intType NOT IN (34, 35) AND Type = 1
    OR @intType IN (34, 35) AND [use] = 1 AND (
         Type IN (2, 4)
      OR @intType = 35 AND Type = 8
      OR @intType = 34 AND (@blnAllow = 0 OR Type = 8 AND Col1 > 0)
    )
  )

It is assumed that @intType is an int parameter and @blnAllow is a bit parameter.