Help with a sql search query using a comma delimitted parameter

I am looking for something like this but can't figure out the best way to write the query:

SELECT DISTINCT CategoryID FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN ALL (234,245,645)

I am currently building a dynamic query since the IDs are passed in as a comma delimitted string '234,245,645' But as we all know there is no such thing as ALL. Basically I want to return all Categorie开发者_如何转开发s that are have all of the sub categories in the list. Hope this makes sense.

you can do this:

SOLUTION 1

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID)
       = LEN(
          REPLACE(
           REPLACE(
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(
                   REPLACE(
                    REPLACE('234,245,645','0','')
                   , '1', '')
                  , '2', '')
                 , '3', '')
                , '4', '')
               , '5', '')
              , '6', '')
             , '7', '')
            , '8', '')
           , '9', '')
          , ' ', '')) + 1

SOLUTION 2: another that may work:

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID) 
       = (SELECT COUNT(DISTINCT SubcategoryID)
            FROM SubCategories 
           WHERE SubcategoryID IN (234,245,645))

You could write your own Split-Function:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  

Then you can join or select the table that is returned from this Table-Valued User-Defined Function.

Create a split function to convert the CSV into a table value, then join the table value function to your select clause to limit the results. See http://phelabaum.com/archive/tag/tally-table/

Quick example (requires you to create a Tally table as detailed over here http://www.sqlservercentral.com/articles/T-SQL/62867/):

CREATE FUNCTION [dbo].[TallySplit] (@Delim CHAR(1), @String VARCHAR(8000))
    RETURNS TABLE AS 
    RETURN (
    SELECT SUBSTRING(@Delim + @String + @Delim,N+1,CHARINDEX(@Delim,@Delim + @String + @Delim,N+1)-N-1) ListValue
    FROM Tally
    WHERE N < LEN(@Delim + @String + @Delim)
    AND SUBSTRING(@Delim + @String + @Delim,N,1) = @Delim 
    )
GO

Then write your select like so:

DECLARE @vCatIDs varchar(max)
SET @vCatIDs = '234,245,645'
SELECT DISTINCT CategoryID FROM tbl_Categories c 
  INNER JOIN mappingTable mp ON c.CategoryID = mp.CategoryID 
  INNER JOIN SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
  INNER JOIN dbo.TallySplit(',',@vCatIDs) ts ON ts.ListValue = sc.SubCategoryID

-- Parameter string
declare @ParamStr varchar(100) = '234,245,645'

-- Convert param to xml
declare @XMLStr xml = convert(xml, '<r>'+replace(@ParamStr, ',', '</r><r>')+'</r>')

-- Store param values in table variable
declare @T table (ID int)
insert into @T 
select r.value('.', 'int')
from @XMLStr.nodes('r') r(r)

-- Get the number of parameters
declare @ParamCount int = (select count(*) from @T)

-- Get the categoryids
select CategoryID
from mappingTable
where SubCategoryID in (select id from @T)
group by CategoryID
having count(CategoryID) = @ParamCount

DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = '234,245,645,';
SET @Delimiter = ',';

;WITH Strings(s, r)
AS
(
SELECT
SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,
SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Strings
WHERE
CHARINDEX(@Delimiter, r) > 0
)

SELECT  CategoryId
FROM    (
            SELECT  c.CategoryID
            FROM    tbl_Categories c 
            JOIN    mappingTable mp 
                ON  c.CategoryID = mp.CategoryID 
            JOIN    SubCategories sc 
                ON  mp.SubCategoryID = sc.SubCategoryID
            WHERE   sc.SubcategoryID IN (SELECT s FROM Strings)
        ) x
GROUP BY CategoryId
HAVING COUNT(*) = (SELECT count(*) FROM Strings)

String-split copy from here. Note that you need a trailing ','.

Other implementations of string-split may be better for you, but this shows how to handle the 'ALL'-condition.

Not sure if it's really that what you want as your question is a bit unclear.

DECLARE @SQL NVARCHAR(MAX) 
DECLARE @IDs NVARCHAR(MAX)

SET @IDs = '234,245,645'

SET @SQL = 'SELECT DISTINCT CategoryID FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN  (' + @IDs + ')'


exec (@sql)