Define Variable to Use with in Operator (T-Sql)

SQL Server - In clause with a declared variable

You need to execute this as a dynamic sp like

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'

exec sp_executesql @sql

TSQL Variable With List of Values for IN Clause

You can use a variable in an IN clause, but not in the way you're trying to do. For instance, you could do this:

declare @i int
declare @j int

select @i = 10, @j = 20

select * from YourTable where SomeColumn IN (@i, @j)

The key is that the variables cannot represent more than one value.

To answer your question, use the inline select. As long as you don't reference an outer value in the query (which could change the results on a per-row basis), the engine will not repeatedly select the same data from the table.

Using variable with SQL WHERE IN statement

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

Set variable with multiple values and use IN

You need a table variable:

declare @values table
(
    Value varchar(1000)
)

insert into @values values ('A')
insert into @values values ('B')
insert into @values values ('C')

select blah
from foo
where myField in (select value from @values)

Executing a statement with logical operator as a variable

If I understand well, you may need something like the following:

declare
    /* declare a variable to host a SQL query */
    vSQL    varchar2(1000);
    vResult varchar2(1000);
begin    
    /* build the SQL query as a string and save it into the variable */
    select 'select case ' || chr(13) || chr(10) ||
           listagg ( 'when ' || n1.num || op || n2.num || ' then ''' || n1.num || op || n2.num || '''', chr(13) || chr(10)) 
           within group ( order by 1)
           || ' end' || chr(13) || chr(10) ||
           'from dual'
    into vSQL
    from ( select '<'  as op from dual union all
           select '>'        from dual union all
           select '>='       from dual union all
           select '>='       from dual
         ) operators
    cross join (
                select level as num
                from dual
                connect by level <= 2
               )  n1
    cross join (
                select level -1 as num
                from dual
                connect by level <= 1 
               ) n2;
    --
    /* print the query */
    dbms_output.put_line(vSQL);
    /* run the dynamic query just built and get the result */
    execute immediate vSQL into vResult;
    /* print the result */
    dbms_output.put_line(vResult);
end;    

When run, this gives:

select case 
when 1<0 then '1<0'
when 1>0 then '1>0'
when 1>=0 then '1>=0'
when 1>=0 then '1>=0'
when 2<0 then '2<0'
when 2>0 then '2>0'
when 2>=0 then '2>=0'
when 2>=0 then '2>=0' end
from dual
1>0

With variables, this:

declare
    vNumVar1    number;
    vNumVar2    number;
    vOpVar      varchar2(2);
    vSQL        varchar2(100);
    vResult     varchar2(100);
begin
    vNumVar1 := 1;
    vNumVar2 := 3;
    vOpVar   := '<='; 
    vSQL := 'select case when ' || vNumVar1 || vOpVar || vNumVar2 || ' then ''something'' end from dual';
    dbms_output.put_line(vSQL);
    execute immediate vSQL
    into vResult;
    dbms_output.put_line(vResult);
end;

gives:

select case when 1<=3 then 'something' end from dual
something

---

Related Topics