Set Variable value in exists condition 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]

I want to set my variable inside exists block with t0.Categoryid. How can that be done?

I want to replace then 1 to the category id value...

Candidate

THEN 1 becomes THEN SET @CategoryId = 1. Better... SELECT CategoryId = (case....) – Granny

i want to set @categoryid to the value of select statement.. – Candidate

@JoeTaras - You certainly can not have a SET statement inside of a CASE statement. SET @c = CASE WHEN x THEN y ELSE z END is valid, but CASE WHEN x THEN SET @c = y ELSE SET @c = z END certainly is not. – Lejeune

@MatBailie: Yes, I've updated my comment with SELECT @categoryId = (case...) – Granny

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

Lawrence

CategoryID will never be set equal to 'category id value' as described in the question – Welt

@Welt neither am I setting it to the categoryID anywhere :) if you read the logic it will always be set to 1 or 0. – Lawrence

But the question was to set it to the categoryId instead of 1 or 0 – Steric

Answer 2

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'

Deify

This is nice, although what OP asked for is to have the category ID, so it should be an int and the query should be @any = Categoryid instead. :) Finally, just to play safe, I'd add ; to every statement. – Ricardaricardama

Answer 3

This will return the category id, if it exists, and 0 if it does 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 just returning null if it doesn't exist, or returning an additional @Exists (BIT) to indicate if it exists or not.

Felicitous

Answer 4

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

Borlow

Answer 5

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.

Fanni

i m forced to use exists block..any suggestion – Candidate

Why you are forced to use it? – Borlow

@vishalsharma I dont think you can set category id inside exists block because Exists is used to determin only it contain rows or not in table. – Fanni

Answer 6

You can use this approach in a few lines

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

Realist

Answer 7

The simpler and straight forward way to assign Id to a variable and have 0 in case the row does not exist is to pre-initialize the desired variable with 0 and select the data in it. i.e:

DECLARE @id bigint = 0
SELECT @id = Id FROM Table1 WHERE column1 = '123'
SELECT @id; -- Should select 0 if the row was not available or the relevant Id if the record was found

This is better than checking after the select statement for the NULLS just to reassign the same variable as other people here have suggested.

If you want it to set NULL if it does not exist, you may want to you the query like this:

DECLARE @id bigint = 0
SET @id = (SELECT Id FROM Table1 WHERE column1 = '123')
SELECT @id; -- Should select NULL if the row was not available or the relevant Id if the record was found

Chidester

Answer 8

Answer taken from Microsoft forum, made by David Dye, but in my case it was on point:

DECLARE @permissionID INT;

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

Original answer

Submission

Apart from reading the table twice unnecessarily, this is also a race condition. – Hypocrisy