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...

THEN 1 becomes THEN SET @CategoryId = 1. Better... SELECT CategoryId = (case....) — Joe Taras
– Joe Taras, Commented Mar 11, 2014 at 9:41
i want to set @categoryid to the value of select statement.. — Vishal Sharma
– Vishal Sharma, Commented Mar 11, 2014 at 9:42
@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. — MatBailie
– MatBailie, Commented Mar 11, 2014 at 10:03
@MatBailie: Yes, I've updated my comment with SELECT @categoryId = (case...) — Joe Taras
– Joe Taras, Commented Mar 11, 2014 at 10:08

M.Ali · Accepted Answer · 2014-03-11 09:58:53Z

47

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

edited Mar 11, 2014 at 9:58

answered Mar 11, 2014 at 9:53

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

t-clausen.dk Over a year ago

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

M.Ali Over a year ago

@t-clausen.dk neither am I setting it to the categoryID anywhere :) if you read the logic it will always be set to 1 or 0.

Igor Meszaros Over a year ago

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

Douglas Marttinen · Accepted Answer · 2017-11-23 05:50:03Z

15

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'

answered Nov 23, 2017 at 5:50

Douglas Marttinen

4695 silver badges10 bronze badges

1 Comment

Andrew Over a year ago

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.

Reuben · Accepted Answer · 2014-09-11 04:03:02Z

9

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.

answered Sep 11, 2014 at 4:03

Reuben

4,3063 gold badges52 silver badges60 bronze badges

Comments

Madhivanan · Accepted Answer · 2014-03-11 09:45:23Z

3

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

answered Mar 11, 2014 at 9:45

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

t-clausen.dk · Accepted Answer · 2014-03-11 10:11:56Z

2

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.

edited Mar 11, 2014 at 10:11

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

answered Mar 11, 2014 at 9:42

Amit Singh

8,12922 silver badges30 bronze badges

3 Comments

Vishal Sharma Over a year ago

i m forced to use exists block..any suggestion

Madhivanan Over a year ago

Why you are forced to use it?

Amit Singh Over a year ago

@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.

Brayan Martínez Santana · Accepted Answer · 2021-06-23 15:46:49Z

0

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

answered Jun 23, 2021 at 15:46

Brayan Martínez Santana

1251 gold badge3 silver badges9 bronze badges

Comments

Jamshaid K. · Accepted Answer · 2023-12-28 09:00:32Z

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

Maciej Czapiewski · Accepted Answer · 2021-10-19 08:18:32Z

-1

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

answered Oct 19, 2021 at 8:18

Maciej Czapiewski

1931 silver badge8 bronze badges

1 Comment

GSerg Over a year ago

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

Collectives™ on Stack Overflow

Set Variable value in exists condition sql server

8 Answers 8

3 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related