时间戳

首页 > 解决方案 > 在 where 语句中选择

sql - 在 where 语句中选择

问题描述

我只是想确保我做对了。

我有两张桌子：

Customer:
ID: GUID
name: varchar
... etc
 and Reference
ID: GUID
customerid: GUID ----> FK
Myreference: varchar (max)
timestamp: datetime

如果引用已被其他客户使用，我有以下存储过程检查引用表。

    @CustomerId [uniqueidentifier], 
    @Myreference [nvarchar]
AS 
BEGIN 
    SELECT  
        CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) AS IsInUse
    FROM  Reference
    WHERE CustomerId <> @CustomerId AND Myreference= @Myreference 
END 
GO

现在，我需要更改存储过程以检查引用是否被另一个客户使用，以检查其他客户是否有另一个引用记录，然后返回 0，否则返回 1。

这是我的改变，但我很确定它会更好。

IF (SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) AS IsInUse
   FROM Reference 
   WHERE CustomerId <> @CustomerId AND  
         Myreference = @Myreference ) = 1 
    BEGIN 
        SELECT CAST(CASE WHEN COUNT(*) > 1 THEN 0 ELSE 1 END AS BIT) AS IsFine 
        FROM Reference 
        WHERE CustomerId = (SELECT CustomerId 
                            FROM Purchase.BureauCreditEnquiry 
                            WHERE CustomerId <> @CustomerId AND  
                                  Myreference = @Myreference ); 
    END; 
ELSE 
    BEGIN 
        SELECT 0; 
    END;

标签： sqlsql-serverselect-case

解决方案

因为这是stored procedure，请使用sql变量。

DECLARE @IsInUse INT
DECLARE @isFine INT

SET @IsInUse = SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) AS IsInUse
            FROM Reference 
            WHERE CustomerId <> @CustomerId AND  
            Myreference = @Myreference

SET @isFine = SELECT CAST(CASE WHEN COUNT(*) > 1 THEN 0 ELSE 1 END AS BIT) AS IsFine 
            FROM Reference 
            WHERE CustomerId = (SELECT CustomerId 
                            FROM Purchase.BureauCreditEnquiry 
                            WHERE CustomerId <> @CustomerId AND  
                                  Myreference = @Myreference )

SELECT IIF(@IsInUse >= 1, @isFine, 0)

推荐阅读