CODEWITHSUNDEEP
sqlsql-serversql-server-2008
JAT
JAT

Reputation: 317

SQL select with IN clause

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?

Upvotes: 1

Views: 1279

Answers (3)

Robert
Robert

Reputation: 25753

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;

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

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;

Upvotes: 2

Heinzi
Heinzi

Reputation: 172200

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:

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

Upvotes: 0

Related Questions