在存在条件 sql server 中设置变量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22321749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set Variable value in exists condition sql server
提问by Vishal Sharma
Declare @CategoryID as int
BEGIN
SELECT
(CASE
WHEN EXISTS(
SELECT t0.Categoryid AS [EMPTY]
FROM Categories AS [t0]
WHERE [t0].Categoryname = @CategoryName
) THEN 1
ELSE 0
END) AS [value]
if i want to set my variable inside exists block with t0.Categoryid how could i do that ?
如果我想用 t0.Categoryid 在exists块中设置我的变量，我该怎么做？
what i want is to replace then 1to the category id value...
我想要的是将1替换为类别 ID 值...
thanks in advance..
提前致谢..
回答by M.Ali
Declare @CategoryID as int
SET @CategoryID = CASE WHEN EXISTS(SELECT 1
FROM Categories
WHERE Categoryname = @CategoryName)
THEN 1 ELSE 0
END
Another way would be something like ....
另一种方式是......
IF EXISTS (SELECT 1
FROM Categories
WHERE Categoryname = @CategoryName)
BEGIN
SET @CategoryID = 1;
END
ELSE
BEGIN
SET @CategoryID = 0;
END
回答by Reuben
This will return the category id, if it exists, and 0 if it does not.
这将返回类别 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];
However, I would recommend just returning null if it doesn't exist, or returning an additional @Exists (BIT) to indicate if it exists or not.
但是，如果它不存在，我建议只返回 null，或者返回一个额外的 @Exists (BIT) 以指示它是否存在。
回答by Douglas Marttinen
My 2 cents...
我的 2 美分...
DECLARE @any BIT = 0
SELECT TOP 1 @any = 1 FROM Categories WHERE CategoryName = @CategoryName
IF (@any = 1)
PRINT 'Yes'
ELSE
PRINT 'No'
回答by Amit Singh
Yo can try like this.
你可以这样试试。
Declare @CategoryID as int
Set @CategoryID =0;
SELECT @CategoryID=t0.Categoryid
FROM Categories AS [t0]
WHERE [t0].Categoryname = @CategoryName
IF category name Exists than its assign the category Id of that category otherwise it remains zero.
如果类别名称存在，则为其分配该类别的类别 ID，否则它保持为零。
回答by Madhivanan
Declare @CategoryID as int
SET @CategoryID =
(SELECT t0.Categoryid AS [EMPTY]
FROM Categories AS [t0]
WHERE [t0].Categoryname = @CategoryName)
SET @CategoryID =COALESCE(@CategoryID ,0)
