Set the value of a variable in the condition state of sql server

Question

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 an existing block using t0.Categoryid, how can I do this?

I want to replace , then 1 with the category value <...>

thanks in advance.

Answer 1

 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 

Answer 2

This will return the category identifier if it exists, and 0 if it is not.

 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 simply returning null if it does not exist, or returning an optional @Exists (BIT) to indicate whether it exists or not.

Answer 3

You can try it like this.

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

Category identifier IF There is what assigns the category identifier of this category, otherwise it remains equal to zero.

Answer 4

My 2 cents ...

 DECLARE @any BIT = 0 SELECT TOP 1 @any = 1 FROM Categories WHERE CategoryName = @CategoryName IF (@any = 1) PRINT 'Yes' ELSE PRINT 'No' 

Answer 5

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