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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I have another solution to do it without dynamic query  We can do it with the help of xquery as well       SET  Xml   cast    lt A gt   replace  3 4 22 6014         lt  A gt  lt A gt      lt  A gt    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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2008

Examples related to in-clause