Setting up a variable in a SQL IF-ELSE statement

March 3, 2022

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?

>Solution :

A few ways you might be able to accomplish this.

Using a variable

DECLARE @IntId int;

SET @IntId = (
    /* I added a TOP 1, in case your query returns more
    than one row. If it does return more than 1 row, you're
    looking at looping data to a print statement, and that's
    a different question. */

    SELECT TOP 1 IntId
    FROM MyTable
    WHERE Name = 'Jon'
)

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

IF EXISTS, select something

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