SQL Server - In clause with a declared variable

Question

Let say I got the following :

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

Error : Conversion failed when converting the varchar value ', ' to data type int.

I understand why the error is there but I don't know how to solve it...

@TomTom et al - I disagree that this is a duplicate. The other question covers more ground not related to what this question addresses in the specific. Most importantly, I am glad I found this post and not the other - as this one solved my problem precisely. — qxotk, Feb 27 '18 at 18:47
maybe you can use `string_split` which is available at **[SQL Server 2016](https://www.mssqltips.com/sqlservertip/4884/sql-server-2016-stringsplit-function/)**, change your code to `select * from A where Id not In (select value from string_split('3;4;5;6',';'))` — Guokas, Mar 30 '20 at 03:45

score 64 · Answer 1 · answered Dec 11 '14 at 12:46

This is an example where I use the table variable to list multiple values in an IN clause. The obvious reason is to be able to change the list of values only one place in a long procedure.

To make it even more dynamic and alowing user input, I suggest declaring a varchar variable for the input, and then using a WHILE to loop trough the data in the variable and insert it into the table variable.

Replace @your_list, Your_table and the values with real stuff.

DECLARE @your_list TABLE (list varchar(25)) 
INSERT into @your_list
VALUES ('value1'),('value2376')

SELECT *  
FROM your_table 
WHERE your_column in ( select list from @your_list )

The select statement abowe will do the same as:

SELECT *  
FROM your_table 
WHERE your_column in ('value','value2376' )

this will leads on performance getting down because the second select within the in clause because it will be executed for every row — fatiDev, Dec 12 '17 at 11:15
I am using the same thing, but the issue is that if I use table in my "IN" clause, it takes around 20 seconds. but, if I use a string there, then it runs in less than 2. so I want to avoid using the temporary table and create a string for IN clause. — Gautam, Oct 04 '21 at 18:37

score 47 · Accepted Answer · answered May 31 '10 at 15:51

47

You need to execute this as a dynamic sp like

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3,4,22,6014'
declare @sql nvarchar(Max)

Set @sql='SELECT * FROM [A] WHERE Id NOT IN ('+@ExcludedList+')'

exec sp_executesql @sql

answered May 31 '10 at 15:51

TonyP

5,655
13
60
94

12

Isn't this vulnerable to SQL Injection if ExcludedList is entered by a user? For instance, ExcludedList = '3); DROP TABLE USERS; --' (comment system won't let me use (at) sign) – ristonj Apr 20 '12 at 20:24
1

It does not matter. Multiple variables (one per item) or dynamic SQL are the ONLY ways to handle this. Yes, one has to be careful. Or drop the in clause (and load the items into a temporary table / table variable and then join). – TomTom Dec 11 '14 at 12:51
1

I would prefer to use a string splitter instead of dynamic sql to avoid sql injection. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Sep 01 '16 at 16:37
1

This is valid but not the only answer. There are pros and cons to all the answers that require vetting :) – Mike M Jun 11 '17 at 13:33
Should we use dynamic sql or should we parse and store csv string into a temp table and then use join with main table. I know latter has more steps but just want to check which way will be more effective considering performance. – KnowledgeSeeeker Aug 19 '21 at 14:28
This work if the var is int. How about if the var is varchar? – Ahmad Pujianto Nov 24 '21 at 13:16
@TomTom When the `@ExcludedList` is `NULL` or `Empty` i wanted all the records, How can i do? – A Coder Apr 03 '22 at 09:18

score 21 · Answer 3 · answered Oct 18 '11 at 19:59

21

DECLARE @IDQuery VARCHAR(MAX)
SET @IDQuery = 'SELECT ID FROM SomeTable WHERE Condition=Something'
DECLARE @ExcludedList TABLE(ID VARCHAR(MAX))
INSERT INTO @ExcludedList EXEC(@IDQuery)    
SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

I know I'm responding to an old post but I wanted to share an example of how to use Variable Tables when one wants to avoid using dynamic SQL. I'm not sure if its the most efficient way, however this has worked in the past for me when dynamic SQL was not an option.

answered Oct 18 '11 at 19:59

Carl Osterman

327
2
3

4

I like this one because you avoided using dynamic SQL – Techgration Nov 25 '14 at 13:22
6

I thought `EXEC(@IDQuery)` is dynamic? – David R Tribble Jun 26 '18 at 20:32
You don't *have* to do that, though. I'm not really sure why he did. You can just do it like this. `DECLARE @SomeTableVar TABLE(id INT)` `INSERT INTO @SomeTableVar SELECT some_int FROM some_table` `WHERE some_int IS NOT NULL` `SELECT * FROM @SomeTableVar` I think it's just a contrived example. By the way, sorry for any comment reply spam, guys. I'm a big dummy that didn't understand that comment markdown is different from answer markdown on StackOverflow. :v – Elijah Woodward Sep 10 '19 at 19:58
6

IN(@variable) expects a scalar, not a table variable – Sam Jan 02 '20 at 16:28

score 9 · Answer 4 · answered Aug 29 '14 at 17:22

First, create a quick function that will split a delimited list of values into a table, like this:

CREATE FUNCTION dbo.udf_SplitVariable
(
    @List varchar(8000),
    @SplitOn varchar(5) = ','
)

RETURNS @RtnValue TABLE
(
    Id INT IDENTITY(1,1),
    Value VARCHAR(8000)
)

AS
BEGIN

--Account for ticks
SET @List = (REPLACE(@List, '''', ''))

--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
    SET @List = ''
END

--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN

    INSERT INTO @RtnValue (value)
    SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))  

    SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))

END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END

Then call the function like this...

SELECT * 
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL

This has worked really well on our project...

Of course, the opposite could also be done, if that was the case (though not your question).

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value

And this really comes in handy when dealing with reports that have an optional multi-select parameter list. If the parameter is NULL you want all values selected, but if it has one or more values you want the report data filtered on those values. Then use SQL like this:

SELECT * 
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL

This way, if @ExcludeList is a NULL value, the OR clause in the join becomes a switch that turns off filtering on this value. Very handy...

This is a superb solution. It should be the accepted answer. It also works with varchar items which becomes cumbersome in the dynamic SQL answers. — done_merson, Sep 20 '21 at 16:27

score 9 · Answer 5 · answered May 31 '10 at 15:48

You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.

Dynamic SQL example:

DECLARE @ExcludedList VARCHAR(MAX)
    SET @ExcludedList = 3 + ',' + 4 + ',' + '22'

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT * FROM A WHERE Id NOT IN (@ExcludedList) '

 BEGIN

   EXEC sp_executesql @SQL '@ExcludedList VARCHAR(MAX)' @ExcludedList

 END

Salil · Answer 6 · 2010-05-31T15:45:21.907

1

I think problem is in

3 + ', ' + 4

change it to

'3' + ', ' + '4'

DECLARE @ExcludedList VARCHAR(MAX)

SET @ExcludedList = '3' + ', ' + '4' + ' ,' + '22'

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)

SET @ExcludedListe such that your query should become

either

SELECT * FROM A WHERE Id NOT IN ('3', '4', '22')

or

SELECT * FROM A WHERE Id NOT IN (3, 4, 22)

edited May 31 '10 at 15:45

answered May 31 '10 at 15:36

Salil

46,566
21
122
156

2

That fixes the set statement, but the select query still won't work – Joel Coehoorn May 31 '10 at 15:38
1

This worked for me brilliantly – Vikram Shetty Jan 21 '16 at 11:56

score 1 · Answer 7 · answered May 31 '10 at 15:47

Try this:

CREATE PROCEDURE MyProc @excludedlist integer_list_tbltype READONLY AS
  SELECT * FROM A WHERE ID NOT IN (@excludedlist)

And then call it like this:

DECLARE @ExcludedList integer_list_tbltype
INSERT @ExcludedList(n) VALUES(3, 4, 22)
exec MyProc @ExcludedList

score 1 · Answer 8 · answered Dec 21 '11 at 20:13

I have another solution to do it without dynamic query. We can do it with the help of xquery as well.

    SET @Xml = cast(('<A>'+replace('3,4,22,6014',',' ,'</A><A>')+'</A>') AS XML)
    Select @Xml

    SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

Here is the complete solution : http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/

SQL Server - In clause with a declared variable

8 Answers8

Linked

Related