[sql] SQL Server - In clause with a declared variable

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...

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...

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.

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' )

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

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/

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

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)