Scenario: I am trying to create a query where I want to retrieve multiple values from a table. Doing so by listing the values directly is straightforward, like so:
select * from product.text_data
where text_data.field_label = "Owner" or text_data.field_label = "Admin" or text_data.field_label = "Rule";
Question: Is it possible to do the same query, but instead feeding the list of fields into a variable, and checking the variable directly?
Ex:
set @idlist1 = ("Owner", "Admin", "Rule");
select * from product.text_data
where product.text_data.field_ref in @idlist1;
Issue: If I try to run this, I get the
SQL Error (1241): Operand should contain 1 column(s)
It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.
In your use case, you seem to be looking for dynamic sql.
declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)