在where子句中使用临时表时遇到问题

Question

我正在创建一个报告，用户可以在字段的2种状态之间进行选择。默认的"Completed"one_answers"Any"状态。如果选择的状态不是Completed，则需要忽略或选择所有值。我创建了一个包含所有不同值的临时表，并将其设置为一个局部变量。我试图在我的where子句中使用case statement和这个临时表来处理这个问题，但它不让我。除了将sql设置为字符串和exec'ing之外，还有更好的方法来处理这个问题吗?

Declare @paramCompleted varchar(20) = 'Completed'
Declare @paramCommitmentExamined varchar(20) = 'Requested'
Declare @paramDateFrom Date
Declare @paramDateTo Date
Declare @PurchStatus as table([status] varchar(15))
insert into @PurchStatus([status]) select DISTINCT TX01STAT from dimFile_T order by TX01STAT
--select * from @PurchStatus
SELECT 
    dbo.ParseDate(O.ORDDATE) AS ORDDATE,
    T.TX04STAT,--Commitment Examined & type
    E.EXAMINER,
    T.TX02STAT,--Outside search
    C.COUNTY,
    S.SETTSTAT,
    CASE
        WHEN L.LOANAMT = 0 THEN 'Cash'
        ELSE CAST(L.LOANAMT AS VARCHAR(10))
    END AS LOANAMT,
    T.TX01STAT,--Purchase agreement received
    M.MKTSOURC,
    CASE 
        WHEN C.FIRMFILE LIKE '%PC%' THEN 'Commercial'
        WHEN C.FIRMFILE LIKE '%RC%' THEN 'Commercial'
        ELSE 'Residential'
    END AS FIRMFILE,
    C.FIRMFILE as FILENUM,
    'Metes & Bounds' AS LOTUNIT,
    S.STATCMT
FROM
    dimFile_O O
    LEFT JOIN dimfile_T T ON T.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_E E ON E.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_C C ON C.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_S S ON S.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_L L ON L.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_M M ON M.FIRMFILE = O.FIRMFILE
    LEFT JOIN dimfile_P P ON P.FIRMFILE = O.FIRMFILE
WHERE
    T.TX04STAT IN (@paramCommitmentExamined)
    AND T.TX01STAT IN (
        CASE
            WHEN @paramCompleted = 'Completed' THEN @paramCompleted
            ELSE @PurchStatus
        END)

Answer 1

你需要一个select。但是，我认为in也可以改进:

WHERE T.TX04STAT =  @paramCommitmentExamined AND
      ((@paramCompleted = 'Completed' AND T.TX01STAT = @paramCompleted) OR
       (coalesce(@paramCompleted, '') <> 'Completed' AND T.TX01STAT IN (SELECT status FROM @PurchStatus))
      )

不要对字符串变量使用in。您将开始认为in ('Completed,Requested')检查两个值而不是一个值。