SQL select with IN clause

Question

Here is an example of what I am trying to accomplish.

declare @MenuIDs varchar(max) = '1,2,3,4';

SELECT 
    tMenuMain.MenuId, 
    tMenuMain.MenuRank 
INTO #TempRankTable 
FROM tMenuMain 
WHERE tMenuMain.MenuId IN (@MenuIDs);

select * from  #TempRankTable;

The @MenuIDs varaible is actually a sproc parameter. (I just declared it in the example to explain)

How do I get the select to work, since the IN command only works with comma separated values and not just a string. Other problem I am facing is that tMenuMain.MenuId is an integer column. Is it possible to do a CAST in this situation?

definitely not easy! see [HERE](http://www.sommarskog.se/arrays-in-sql-2008.html). Do the menuIDs have to be in a comma seperated list like that? is there anyway you could put them into a single column table first? — whytheq, Sep 15 '12 at 16:55

score 2 · Accepted Answer · answered Sep 15 '12 at 17:14

You can declare a little helper function that "maps" the string to a table;

CREATE FUNCTION dbo.str2ints(@str nvarchar(max))
RETURNS @ints TABLE (val INT)
AS
BEGIN
  DECLARE @xml XML
  SET @xml = '<root><str>' + REPLACE(@str, ',', '</str><str>') + '</str></root>'
  INSERT INTO @ints(val)
  SELECT str.value('.', 'int') 
  FROM @xml.nodes('//str') AS RECORDS(str)
  RETURN
END

Then you can rewrite your function to use that function for splitting;

declare @MenuIDs varchar(max) = '1,2,3,4';

SELECT 
    tMenuMain.MenuId, 
    tMenuMain.MenuRank 
INTO #TempRankTable 
FROM tMenuMain 
WHERE tMenuMain.MenuId IN (SELECT * FROM dbo.str2ints(@MenuIDs));

select * from  #TempRankTable;

Is there a specific reason that the function is sorting the values? If I pass '3,2,4,1' I get the results in the exact same order as when I pass '1,2,3,4'. — JAT, Sep 15 '12 at 21:08

Robert · Answer 2 · 2012-09-15T17:14:55.960

1

You have to use dynamic SQL and view:

declare @MenuIDs varchar(max) = '1,2,3,4';
declare @SQL varchar(max);

set @SQL = 'create view vTab as
            SELECT tMenuMain.MenuId, tMenuMain.MenuRank                 
            FROM tMenuMain 
            WHERE tMenuMain.MenuId IN ('+@MenuIDs+')';
/*
   Select in @SQL should look like:
            create view vTab as
            SELECT tMenuMain.MenuId, tMenuMain.MenuRank                 
            FROM tMenuMain 
            WHERE tMenuMain.MenuId IN (1,2,3,4)
*/

exec(@SQL)

select * 
into #TempRankTable 
from vTab 

drop view vTab   

select * from  #TempRankTable;

or if you can create table before insert

 declare @MenuIDs varchar(max) = '1,2,3,4';
 declare @SQL varchar(max);

 create table #TempRankTable 
 (
   MenuId ...
   MenuRank ...
 ) 

set @SQL = 'insert into #TempRankTable(MenuId,MenuRank) 
            SELECT tMenuMain.MenuId, tMenuMain.MenuRank                 
            FROM tMenuMain 
            WHERE tMenuMain.MenuId IN ('+@MenuIDs+')';
/*
   Select in @SQL should look like:
            insert into #TempRankTable(MenuId,MenuRank) 
            SELECT tMenuMain.MenuId, tMenuMain.MenuRank                 
            FROM tMenuMain 
            WHERE tMenuMain.MenuId IN (1,2,3,4)
*/

exec(@SQL)

select * from  #TempRankTable;

edited Sep 15 '12 at 17:14

answered Sep 15 '12 at 16:41

Robert

25,425
8
67
81

I've tried that before; I get this error: (4 row(s) affected) Msg 208, Level 16, State 0, Line 17 Invalid object name '#TempRankTable'. – JAT Sep 15 '12 at 16:44
@Parado original answer would have been ok but it needed to create the temp table first `CREATE TABLE #TempRankTable (MenuId INT, MenuRank INT)` before the dynamic sql. and the `INSERT INTO` that table within the dynamic sql – whytheq Sep 15 '12 at 17:07
It's true, but I think he doesn't want use `create table`. – Robert Sep 15 '12 at 17:09
Thanks guys, that works great, but is there a way I could avoid the INSERT INTO command? – JAT Sep 15 '12 at 20:55
You can use view, it's first example. – Robert Sep 15 '12 at 20:57

score 0 · Answer 3 · edited May 23 '17 at 11:49

There is easy and clean way to do what you want. The following question contains a lot of workarounds, using, for example, XML or a local function that splits the values:

Passing an array of parameters to a stored procedure

An ugly but easy alternative would be to use the CHARINDEX method to verify if ',' + tMenuMain.MenuId + ',' is contained in ,1,2,3,4, (note the starting and trailing commas).

SQL select with IN clause

3 Answers3