Using variable with SQL WHERE IN statement

Question

I would like to have the list of nodes set at the top of the script, then check if they are in the table. The following code does not throw errors, but the logic is not working. It does not print the message when one of the nodes in the list exists.

DECLARE @nodeId nvarchar(50)
SET @nodeId = 'SomeNodeID, SomeOtherNodeID'
IF EXISTS (SELECT NodeId FROM dbo.tblNodeMaster WHERE NodeId in (@nodeId))
BEGIN
    PRINT RTRIM(CONVERT(varchar(30), GETDATE())) + '  node exists.'
    RETURN
END

Using the following code without a variable it works fine. It will print the message when any node in the list exists.

IF EXISTS (SELECT NodeId FROM dbo.tblNodeMaster WHERE NodeId in ('SomeNodeID', 'SomeOtherNodeID'))
BEGIN
    PRINT RTRIM(CONVERT(varchar(30), GETDATE())) + '  node exists.'
    RETURN
END

You can't use in in that way with a string - it's a set operation, it doesn't parse the string. — Stu
– Stu, Commented Mar 31, 2021 at 19:04
Are you using MS SQL Server? (The above code is very product specific.) — jarlh
– jarlh, Commented Mar 31, 2021 at 19:04

Stu · Accepted Answer · 2021-03-31 19:09:19Z

3

Instead of using a string, you could use a table variable

    Declare @NodeId table (NodeId varchar(30))
    insert into @NodeId
    select 'SomeNodeID' union all select 'SomeOtherNodeID'
    
    IF EXISTS (SELECT * FROM dbo.tblNodeMaster WHERE NodeId in (select NodeId from @nodeId))
...

answered Mar 31, 2021 at 19:09

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Over a year ago

So, I actually have the NodeIDs in a data file. So I just loaded them into a temp table and used that. Thanks for help!

Charlieface Over a year ago

Ever heard of values('SomeNodeID', 'SomeOtherNodeID')

Stu Over a year ago

Haha yes - force of habbit I guess.

Jeff · Accepted Answer · 2021-03-31 21:24:59Z

3

DECLARE @nodeId nvarchar(50) = 'SomeNodeID,SomeOtherNodeID'
IF EXISTS (SELECT * FROM dbo.tblNodeMaster WHERE NodeId IN (SELECT LTRIM(value) FROM STRING_SPLIT(@nodeId, ','))
BEGIN
    PRINT RTRIM(CONVERT(varchar(30), GETDATE())) + '  node exists.'
    RETURN
END

You can use STRING_SPLIT as a sub-query to the IN operator.

answered Mar 31, 2021 at 21:24

Jeff

5472 silver badges8 bronze badges

Collectives™ on Stack Overflow

Using variable with SQL WHERE IN statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related