'Setting up a variable in a SQL IF-ELSE statement

I have the following statement:

IF (SELECT IntId 
FROM MyTable
WHERE Name = 'Jon') IS NOT NULL
BEGIN
    PRINT IntId
END
ELSE
BEGIN
    PRINT 'Not an id'
END

The IntId is the PK of the table and I want to see if it exists for the Name = Jon but it is out of scope so how do I get it to print?

sqlsql-servertsql


Solution 1:[1]

What you could do in this scenario if using SQL Server 2017+ is use string_agg which would give you all ID values if there's duplicates:

declare @id varchar(max);
select @id=String_Agg(IntId, ',')
from MyTable
where [Name] = 'jon';

print isnull(@id, 'not an id');

Solution 2:[2]

You have two option. Repeat that select again which is not recommended because of performance issue. and the second it to store in a variable.

Declare @IntId int
SELECT @IntId = IntId 
FROM MyTable
WHERE Name = 'Jon'

IF (@IntId) IS NOT NULL
BEGIN
    PRINT @IntId
END
ELSE
BEGIN
    PRINT 'Not an id'
END

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Meyssam Toluie

Related Questions