Using a list from a @variable in a where statement

Question

I'm trying to use the value from a variable being passed through from elsewhere in my where statement but i'm struggling on syntax.

I have a field that is being passed through where the value with in it is essentially a list

ie @Var1 where the value being passed through is AA','BB','CC (ie its missing the leading ' and trailing '

i want to use this value in my where statement as a list eg

where 
field1 IN @Var1

ie - the actual result I want is

where
field1 IN ('AA','BB','CC')

I don't have any control over what is being passed through (ie AA','BB','CC will always be missing the leading and trailing ', so how do i use that info in the IN clause in the WHERE?

I seem to be coming up against issues with syntax around the use of the '

I hope that makes sense, its not the easiest thing to explain and its still relatively new to me

and just to be clear the value in the variable can be different each time as its a user selected thing - eg could be AA','BB','CC one time but then EE','FF','AA the next time but each time the first code is missing the opening ' and the last code is missing the closing ' — SQL_newbie, Feb 10 '21 at 14:21
Don't use a scalar variable for this, as the name suggests they have a scalar value. Use a table value type. — Thom A, Feb 10 '21 at 14:23
Does this answer your question? [SQL Server - In clause with a declared variable](https://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable) — GSerg, Feb 10 '21 at 14:26
Does this answer your question? [Insert multiple rows with parameters Sql Server](https://stackoverflow.com/questions/38521908/insert-multiple-rows-with-parameters-sql-server) — Charlieface, Feb 10 '21 at 14:27
God that accepted answer is terrible, @GSerg ... it's an injection nightmare. — Thom A, Feb 10 '21 at 14:27
"essentially a list" - no, it isn't. It's a string that happens to contains some commas. I know of no language which will take such a value, provided as a single parameter, and suddenly decide to interpret it as multiple parameters. You should use a type *designed* for holding multiple values, such as a table variable, or if not that, XML or JSON. — Damien_The_Unbeliever, Feb 10 '21 at 14:27
@Larnu There are multiple options, and https://stackoverflow.com/q/337704/11683 is referenced at the top. — GSerg, Feb 10 '21 at 14:28
There can only be one accepted answer, @GSerg . My (implied) point is that if you are suggesting that dupe you recommend that they ***not*** use the accepted solution as it's severely flawed. Far too many users use the "first" solution, which can very often be one of the worst as much as the best. — Thom A, Feb 10 '21 at 14:30
I'm not sure if it is the best solution, but if you absolutely can't change the format the parameter is passed to you, you could try the following: Create a procedure, which accepts a nvarchar(4000) as parameter. In the procedure declare a table variable with one varchar columnb and a XML variable which then receives the values from your string. Populate the table variable with the node values from the XML variable via xml.nodes. Last but not least, perform your query in this procedure and perform an inner join to your table variable. — Tyron78, Feb 10 '21 at 15:11
_... being passed through from elsewhere ..._ This is the root of your problem. Do not pass ot store what effectively is a table (or array if you like) as a scalar variable. I suggest you bookmark and read the very good information Erland has written on his site - discussion on [arrays](http://www.sommarskog.se/arrays-in-sql.html) provides a starting point for the background you need. — SMor, Feb 10 '21 at 15:39

score 0 · Answer 1 · answered Feb 10 '21 at 15:18

Here an example for my approach in the comments above:

CREATE PROCEDURE test_r(@x varchar(4000))
AS
BEGIN
  DECLARE @t TABLE (keys NVARCHAR(10))

  DECLARE @sql_xml XML = CAST('<root><X>'+REPLACE(@x, char(39)+','+char(39), '</X><X>') + '</X></root>' AS XML)

  INSERT INTO @t(keys)
    SELECT f.x.value('.', 'VARCHAR(10)') AS y
      FROM @sql_xml.nodes('/root/X') f(x)
 
   SELECT *
      -- FROM [MyTable] m INNER JOIN...
     FROM @t
END
GO

DECLARE @x AS nvarchar(4000) = 'AA' + char(39) + ',' + char(39) + 'BB' + char(39) + ',' + char(39) + 'CC'
EXEC test_r @x

Depending on what you have to do with the result of your query, you might want to create a function instead of a procedure.

@Charlieface I guess nothing is wrong with it - except that as far as I know `STRING_SPLIT` is not supported in all versions on SQL Server. `OPENJSON` might work as well - I'm more familiar with XML, therefore the example with XML... — Tyron78, Feb 11 '21 at 09:13

Using a list from a @variable in a where statement

1 Answers1