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

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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 case when @id is null then 'not an id' else @id end;