How to DECLARE variable and set values to a field in a table

I am trying to set values but need to use a field, rather than inputting hundreds of values.

Current code:

DECLARE @variable AS VARCHAR (100)
SET @variable = 'Y'

I need to be able to use a field as the value:

SET @variable = tbl.field

I have also tried


DECLARE @variable AS table (val varchar (100))
insert into @variable (val)  
    (SELECT 
     distinct field 
     FROM
     tbl])

select * from @variable

SELECT * FROM tbl 

WHERE field = @variable

However this code simply runs both at the same time, creating two outputs, so I am missing a link here

I need to be able to run the code so that all available values are set as each option needs to be tested at once.

Solution

You declared a scalar variable. It holds only one value, and cannot hold more than one value.

In this approach, you can store multiple values.

DECLARE @variable AS TABLE(val VARCHAR(50))
INSERT INTO @variable(val) VALUES
('Y'),
('N')

SELECT * FROM @variable