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?
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');
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'
ORDER BY IntId
)
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')
BEGIN
SELECT IntId FROM MyTable WHERE Name = 'Jon'
END
ELSE
BEGIN
PRINT 'Not an id'
END
TOP 1 in that subquery, as it simply hides the problem. If you are going to use a TOP 1 at least put an ORDER BY there so that it doesn't return an arbitrary value.
ORDER BY for consistent results though. Either remove the TOP or add the ORDER BY.
IF EXISTS (SELECT 1 FROM MyTable WHERE Name = 'Jon') IS NOT NULL provides the blessing "Incorrect syntax near the keyword 'IS'." Since exists returns a boolean result which cannot be null there isn't much need to check for null. Aside: Performing two queries to get one result isn't likely to improve performance in this case.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
