-2

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Becky Purland
  • 89
  • 10
  • A **scalar** value cannot hold a tuple; it's a *scalar* variable. If you want to store multiple values in a variable use a table type variable. – Thom A Dec 12 '22 at 10:54
  • *"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."* You need to elaborate on this, as this statement doesn't explain anything. The above *does* `INSERT` all the values in one go, so I'm not sure why you are saying it isn't doing it all at once. – Thom A Dec 12 '22 at 12:11
  • @larnu please read the whole question and not just the last sentence. That part wasn't the edit – Becky Purland Dec 12 '22 at 12:16
  • I have. Your code works fine ([db<>fiddle](https://dbfiddle.uk/FnmG5uDX)) and you don't explain why the results aren't what you want; if you can't explain that we can't help you. – Thom A Dec 12 '22 at 12:19
  • I did say, underneath the last piece of code. – Becky Purland Dec 12 '22 at 12:22
  • I added a where clause 'where field = @variable but this will not run at all. error message 'Must declare the scalar variable "@variable".' – Becky Purland Dec 12 '22 at 12:22
  • Because you haven't defined the **scalar** variable `@variable`, @Becky. The `@variable` you define is a **table** type variable; it works like a `TABLE`. You need to use a `JOIN`, `IN` or `EXISTS` like you would with a `TABLE`. – Thom A Dec 12 '22 at 12:46
  • @larnu could you please provide an example using my code? – Becky Purland Dec 12 '22 at 12:53
  • Does this answer your question? [Check if entry in table A exists in table B](https://stackoverflow.com/questions/15938180/check-if-entry-in-table-a-exists-in-table-b) Though as you are `INSERT`ing values from `tbl` and then checking if those values exist in `tbl`, then you'd get *every* row apart from where the column doesn't have the value `NULL`, so why not just check for `NULL`? – Thom A Dec 12 '22 at 12:54
  • @larnu This is just a basic example for the purposes of asking the question, what I am trying to do is far more complicated, but it's not really appropriate to type 300 lines of code in here, when this small example will suffice. To clarify, I am not looking for null values. I have a report which has filters based on 8 parameters, each parameter has over 100 options to choose from. The parameters are determined in the code. So I cannot run the code without declaring the values for each parameter. However, I need to be able to run the code for every option in each parameter. – Becky Purland Dec 12 '22 at 13:10
  • 1
    This definitely sounds like you've not told us the full story. Rather than invalidating the existing answers, I suggest you post a new question (as I suggested before) as explain the actual problem in full. What we have here is an [XY Problem](//xyproblem.info). Tell us what X is, not Y, in your new question. – Thom A Dec 12 '22 at 13:12
  • As I stated before, I am now not allowed to ask anymore questions, so we are stuck with this comment thread. – Becky Purland Dec 12 '22 at 13:22

3 Answers3

1

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

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Govind
  • 186
  • 5
0

As Larnu wrote, you can't assign a scalar value to hold two values.

My suggestions are using a temporary table to hold all of your values, or assign both values as one and break them with STRING_SPLIT.

For example:

DECLARE @variable VARCHAR(100) = 'Y,N'  
SELECT value  
FROM STRING_SPLIT(@variable, ',')
Yarin
  • 1
  • 1
-3

A scaler variable only can store a single value in each set/select statement.

MehD
  • 1
  • 2