Declare @CategoryID as int
SET @CategoryID =  CASE WHEN EXISTS(SELECT 1
                                    FROM  Categories
                                    WHERE Categoryname = @CategoryName)
                     THEN 1 ELSE 0
                   END

另一种方法是......

IF EXISTS (SELECT 1
           FROM  Categories
           WHERE Categoryname = @CategoryName)
 BEGIN
   SET @CategoryID = 1;
 END
ELSE
 BEGIN
   SET @CategoryID = 0;
 END

我的两分钱...

DECLARE @any BIT = 0
SELECT TOP 1 @any = 1 FROM Categories WHERE CategoryName = @CategoryName

IF (@any = 1)
  PRINT 'Yes'
ELSE
  PRINT 'No'

这将返回类别id（如果存在），如果不存在则返回0。

SET @CategoryID = null;

SELECT @CategoryID = t0.Categoryid
FROM Categories AS [t0]
WHERE [t0].Categoryname = @CategoryName;

IF @CategoryID IS NULL
    SET @CategoryID = 0;

SELECT @CategoryID AS [value];

但是，我建议如果它不存在就返回null，或者返回一个额外的@Exists（BIT）来指示它是否存在。

Declare @CategoryID as int
SET @CategoryID =
            (SELECT t0.Categoryid AS [EMPTY]
            FROM Categories AS [t0]
            WHERE [t0].Categoryname = @CategoryName)
SET @CategoryID =COALESCE(@CategoryID ,0)

你可以试试这样。

Declare @CategoryID as int
 Set @CategoryID =0;
    SELECT @CategoryID=t0.Categoryid
    FROM Categories AS [t0]
    WHERE [t0].Categoryname = @CategoryName

如果category name存在，则为其分配该类别的类别ID，否则保持为零。

您可以在几行代码中使用此方法

DECLARE @CategoryID int
SELECT @CategoryID = Categoryid FROM Categories WHERE Categoryname = @CategoryName

IF @CategoryID IS NULL
    THROW 50000, N'not found', 1 -- do something
    
SELECT @CategoryID -- the Categoryid value

答案取自微软论坛，由大卫染料，但在我的情况下，它是对点：

DECLARE @permissionID INT;

IF EXISTS(SELECT Id FROM Permission WHERE [Description] = 'SettlementReport')
    SET @permissionID = (SELECT Id FROMPermission
    WHERE [Description] = 'SettlementReport')

Original answer