CODEWITHSUNDEEP
sql-servert-sqlvariableswhere-clause
Andrew
Andrew

Reputation: 61

Using variable with SQL WHERE IN statement

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

Upvotes: 0

Views: 4369

Answers (2)

Jeff
Jeff

Reputation: 537

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.

Upvotes: 3

Stu
Stu

Reputation: 32614

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))
...

Upvotes: 3

Related Questions