SQL Server : store multiple values in variable in stored procedure

Question

I am trying to add multiple value to a variable in my stored procedure. My requirement is to store multiple values in a variable and use it again later to delete the values.

DECLARE @EmpID

SET @EmpID = (SELECT e.id 
              FROM employee e,Technology t
              WHERE e.status = 'InActive'
                AND e.id = t.Mainid
              UNION
              SELECT e.id 
              FROM employee e,Technology t
              WHERE e.status = 'InActive'
                AND e.id = t.AssociateID)

DELETE FROM Technology
WHERE Mainid IN (@EmpID) OR AssociateID IN (@EmpID)

When I tried this query, I'm getting an error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

A *scalar* variable can only contain **one** atomic value. Use a table type parameter/variable. — Thom A, Jun 02 '21 at 16:15
Also even before the subquery error you'll get an error *"Incorrect syntax near the keyword 'Set'"* due to not specifying a data type for your scalar variable `@EmpID`. — Thom A, Jun 02 '21 at 16:17
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**almost 30 years** ago) and its use is discouraged — marc_s, Jun 02 '21 at 16:25
Finally, stop using that old 1980's syntax. The explicit JOIN syntax came out in 1992; almost 30 years ago. [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) (Marc beat me to it by seconds, but leaving this year to really drive the nail in). — Thom A, Jun 02 '21 at 16:25
If you don't need the IDs for anything but the deleting: `WITH RowsToDelete AS (SELECT T.* FROM Technology T JOIN Employee E ON E.status = 'InActive' AND (E.id = T.Mainid OR E.id = T.AssociateID) DELETE RowsToDelete`. No unions, variables or other more complicated constructs needed. As a bonus, you can use `SELECT * FROM RowsToDelete` first before deleting to see what would actually go. — Jeroen Mostert, Jun 02 '21 at 16:30
Does this answer your question? [Set variable with multiple values and use IN](https://stackoverflow.com/questions/7431341/set-variable-with-multiple-values-and-use-in) — pwilcox, Jun 02 '21 at 18:54
Thanks @marc_s, I will avoid using this kind of syntax for joins further — user15795435, Jun 03 '21 at 06:48

score 1 · Accepted Answer · answered Jun 02 '21 at 16:33

As noted you cannot treat a variable as an array, you need to use either a table variable or temporary table.

To do so would look something like the following (note I've also used the preferable and readable join syntax)

declare @EmpId table (Id int)

insert into @EmdId(Id)
select e.id 
from employee e join Technology t on e.id = t.Mainid
where e.status = 'InActive'
union
select e.id 
from employee e join Technology t on e.id = t.AssociateID
where e.status = 'InActive'

delete from t
from @EmpId e join Technology t 
    on t.Mainid = e.Id or t.AssociateID=e.Id

However I don't see any need to store the Ids of rows just to delete them. Just delete the rows directly - there are numerous ways you can do so, one would be to use exists to correlate with the Employees table:

delete from t
from Technology t 
where exists (select * from employee e where e.id = t.Mainid and e.status 'InActive')
or exists (select * from employee e where e.id = t.AssociateID and e.status 'InActive')

you can run the above as a select * from to validate the rows to be deleted.

You can simplify: `where exists (select 0 from employee e where e.status = 'InActive' and e.id in (t.mainId, t.associateId))` — pwilcox, Jun 02 '21 at 18:50
Thanks @pwilcox - yes that's possible, I would probably want to investigate the resulting execution plan, there would be a risk that might produce an index scan rather than possibly two lower-cost index seeks; depending on the data and table indexes. — Stu, Jun 02 '21 at 18:54

critical_error · Answer 2 · 2021-06-02T18:54:47.943

-2

Given you need to store the values for later use, you can accomplish this using the XML datatype.

SET NOCOUNT ON;

DECLARE @Employees table ( id int, [status] varchar(10) );
    INSERT INTO @Employees VALUES ( 1, 'InActive' ), ( 2, 'Active' );

DECLARE @Technology table ( AssociateID int, MainID int );
    INSERT INTO @Technology VALUES ( 1, 1 ), ( 2, 2 );

DECLARE @EmpIds xml = (

    SELECT * FROM (
        SELECT
            e.id 
        FROM @Employees AS e, @Technology AS t
        WHERE 
            e.[status] = 'InActive'
            AND e.id = t.MainID
        UNION
        SELECT
            e.id 
        FROM @Employees AS e, @Technology AS t
        WHERE
            e.[status] = 'InActive'
            AND e.id = t.AssociateID
    ) AS x
    FOR XML PATH ( '' ), ROOT ( 'employeeIds' )

);

DELETE FROM @Technology WHERE MainID IN (
    SELECT x.f.value( '.', 'int' ) FROM @EmpIds.nodes( '//employeeIds/id' ) x( f )
) OR AssociateID IN (
    SELECT x.f.value( '.', 'int' ) FROM @EmpIds.nodes( '//employeeIds/id' ) x( f )
);

edited Jun 02 '21 at 18:54

answered Jun 02 '21 at 18:19

critical_error

6,306
3
14
16

Not sure why the downvote. The request explicitly states `My requirement is to store multiple values in a variable and use it again later to delete the values.` This accomplishes that. – critical_error Jun 02 '21 at 18:54
Sorry for not explaining. Sometimes meeting a stated requirement is not a great idea if the requirement itself is questionable given the context. Here, I believe the OP and others in a similar situation are far better off being told NOT to attempt their goal. And showing them how to meet their goal with no caveats stating that it's a bad idea can lead them down a bad path. That's why I downvoted. – pwilcox Jun 02 '21 at 18:57
Not sure what you have against table variables or temp tables, but `xml` is not the solution – Charlieface Jun 02 '21 at 19:43

SQL Server : store multiple values in variable in stored procedure

2 Answers2