如果嵌套查询没有返回结果，则将变量设置为 NULL

Question

假设您有以下查询。如果嵌套查询返回 NULL（0 个结果），存储过程将崩溃并出现下面提到的错误。我发现我可以在下面的替代查询中重写代码，但我想找到一种更简单的语法来编写它。我有大约 10 个这样的查询，其中一些有多个嵌套查询。有没有更简单的方法来编写它们？我不是 SQL 专家，所以我一直在寻找建议！谢谢。

查询（有时会崩溃）：

SET @sampleid = (
    SELECT 
        [sampleid]
    FROM [sample]
    WHERE [identifyingnumber] = @sample_identifyingnumber

查询错误：

Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

永不崩溃的替代查询：

IF 
(
    SELECT 
        COUNT([sampleid])
    FROM [sample]
    WHERE [identifyingnumber] = @sample_identifyingnumber
) = 0
BEGIN
SET @sampleid = NULL
END
ELSE
BEGIN
SET @sampleid =
(
    SELECT 
        DISTINCT [sampleid]
    FROM [sample]
    WHERE [identifyingnumber] = @sample_identifyingnumber
)
END

============ ===

更复杂的示例：

SET @testcodeid = (
    SELECT 
        [testcodeid]
    FROM [testcode]
    WHERE [testcode].[name] = (
        SELECT [test_code]
        FROM [ws_test_request]
        WHERE [client_id] = @clientid
          AND [sample_specimen_id] = @sample_identifyingnumber
    )
);  

Answer 1

尝试

SELECT @sampleid = [sampleid]
FROM [sample]
WHERE [identifyingnumber] = @sample_identifyingnumber

Answer 2

您将sample_table中不存在的记录设置为NULL，其中identifyingnumber等于sample_identifyingnumber：

UPDATE my_table
   SET sampleid = NULL
 WHERE NOT EXISTS
          (SELECT 'X'
             FROM sample_table
            WHERE identifyingnumber = sample_identifyingnumber);

您将sample_table记录中存在的字段sampleid设置为MIN（DISTINCT Sampleid），其中identifying_number = sample_identifyingnumber：

UPDATE my_table
   SET sampleid =
          (SELECT MIN (DISTINCT sampleid)
             FROM sample_table
            WHERE identifyingnumber = sample_identifyingnumber)
 WHERE EXISTS
          (SELECT 'X'
             FROM sample_table
            WHERE identifying_number = sample_identifyingnumber);

我已编写MIN（DISTINCT .. .）如果我们发现更多的样本ID对应于相同的识别号码：在这种不幸的情况下，我取X个（X严格> 1）不同样本ID中的最小值。

我需要两次更新而不是一次。