CODEWITHSUNDEEP
sqlsql-serversql-server-2008exists
Vishal Sharma
Vishal Sharma

Reputation: 2803

Set Variable value in exists condition sql server

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

Upvotes: 35

Views: 144328

Answers (8)

Jamshaid K.
Jamshaid K.

Reputation: 4547

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

Upvotes: 0

Maciej Czapiewski
Maciej Czapiewski

Reputation: 143

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

Upvotes: -1

Brayan Martínez Santana
Brayan Martínez Santana

Reputation: 125

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

Upvotes: 0

Douglas Marttinen
Douglas Marttinen

Reputation: 469

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'

Upvotes: 15

Reuben
Reuben

Reputation: 4266

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.

Upvotes: 9

Amit Singh
Amit Singh

Reputation: 8109

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.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: 44

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 3

Related Questions