Sql Server Store Multiple Values in SQL Variable

SQL Server store multiple values in sql variable

You can use a table variable:

declare @caroptions table
(
    car varchar(1000)
)

insert into @caroptions values ('BMW')
insert into @caroptions values ('Toyota')
insert into @caroptions values ('Nissan')

select * from cars where make in (select car from @caroptions)

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)

How to store multiple values in a SQL Server variable

You can declare a variable of type table

DECLARE @tblTrans TABLE (
    tranID INT
);

INSERT INTO @tblTrans 
SELECT DOCUMENT_SET_TRANSACTION.DOCUMENT_SET_TRANSACTION_ID 
FROM ESG.DOCUMENT_SET_TRANSACTION 
WHERE DOCUMENT_SET_TRANSACTION.IDENTIFIER 
    IN (SELECT identifiers FROM @envelopeId);

Depending on what you want to do with the values after this, you could declare a cursor to loop through them or select straight from the variable.

You could also look into using a temporary table depending on what scope you need.

SQL Server : store multiple values in variable in stored procedure

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.

How to store multiple values from SELECT in SQL Server?

SELECT @Value1 = One, @Value2 = two FROM dummyTable;

You don't have any arrays in SQL Server.

Store multiple variables in SQL variable and use it for deleting entries in a table

You can not store multiple values to a scalar variable, you need to have a table type to store the multiple values.

You can try like following using a temporary table.

DECLARE @subjob NVARCHAR(20); 

SELECT subjobfamily 
INTO   #t 
FROM   dbo.jobz 
WHERE  jobfamily = @MainJobId 

DELETE dbo.jobz 
WHERE  jobfamily = @MainJobId 

DELETE dbo.relationship1 
WHERE  subjobs IN (SELECT subjobfamily 
                   FROM   #t) 
drop table #t

Also you need to specify the length of your @subjob variable, if you don't specify the length, it will just take the first character only.

Change following declaration

declare @subjob nvarchar;

to something like

declare @subjob nvarchar(100)

How to store multiple values in single field in SQL database?

You could use a second table to store the numbers, and link back with a Foreign Key:

PersonTable: PersonId, Name, etc..

The second table will hold the numbers...

NumbersTable: NumberId, PersonId(fk), Number

You could then get the numbers like this...

SELECT p.Name, n.Number from PersonTable p Left Join NumbersTable n
on p.PersonId = n.PersonId

This is a simple example. I have used a LEFT JOIN here in case a person doesn't supply their number. Also, this is just pseudo code, so don't use Table in the name.

---

Related Topics