如何检查值是否存在？

we have a table (sales plan form MDS) like

我们有一张表（MDS的销售计划表）

> 20171201 2017 12 2.155.125 ...some other values 
> 20171101 2017 11 2.155.125 ...some other values

and I want to check wheter a record for current month exists (say 2018/01).

我想查看当月的记录（比如2018/01）。

I've tried:

我试过了：

IF EXISTS
    (
        SELECT
            spc.DateID
        FROM
            dim.SalesPlanCountry spc
            JOIN dim.Calendar cal ON spc.DateID = cal.DateID
        WHERE
            cal.CalendarYear = @Year
            AND cal.MonthOfYear = @Month
    ) THEN 
        SELECT 'OK'
    ELSE SELECT 'Missing'

which returns

Incorrect syntax near the keyword 'THEN'.

关键字'THEN'附近的语法不正确。

Help would be appreciated.

帮助将不胜感激。

3 个解决方案

#1

Like I said you don't need the THEN keyword, just use SELECT <values> or use a CASE WHEN statement (see phoniq answer)

就像我说你不需要THEN关键字一样，只需使用SELECT 或使用CASE WHEN语句（参见phoniq答案）

IF EXISTS (
    SELECT
        spc.DateID
    FROM dim.SalesPlanCountry spc
    JOIN dim.Calendar cal
        ON spc.DateID = cal.DateID
    WHERE cal.CalendarYear = @Year
        AND cal.MonthOfYear = @Month
)
SELECT 'OK'
ELSE 
SELECT 'Missing'

#2

SELECT CASE WHEN spc.DateID = NULL THEN 'OK' ELSE 'Missing' END
FROM
  dim.SalesPlanCountry spc
  JOIN dim.Calendar cal ON spc.DateID = cal.DateID
WHERE
  cal.CalendarYear = @Year
  AND cal.MonthOfYear = @Month

#3

Could this not be simplified down to...

难道这不能简化为......

SELECT CASE COUNT(*) WHEN 0 THEN 'Missing' ELSE 'OK' END
FROM dim.SalesPlanCountry spc
     JOIN dim.Calendar cal ON spc.DateID = cal.DateID
WHERE cal.CalendarYear = @Year
  AND cal.MonthOfYear = @Month;

No need for an EXISTS. When doing a COUNT of a dataset, a value will always be returned, even if the data set returns no rows.

不需要EXISTS。在执行COUNT个数据集时，即使数据集没有返回任何行，也将始终返回一个值。

#1