SQL Server 2008-如何获取具有特定ID范围的记录

在SQLServer2008中,我想创建一个存储过程来选择特定的产品,并将它们的ID作为参数传递。

但我打算带来一系列的产品。

不需要的范围(如productId 1和productId 8,或productId 1和productId 29,或productId 8和productId 29)不会显示为结果。

如何从查询结果中过滤那些不需要的范围?

CREATE PROCEDURE [dbo].[ListProduct]
(
    @categoryId
    @productIds VARCHAR(4000) --Suppose we want to bring ONLY productIds 1 AND 8 AND 29 
)
AS
BEGIN

    SELECT 

    category_id,
    product_id, 
    product_name,
    FROM Product
    WHERE category_id = @category_id
    AND productId = --... is it better to use a function? a cursor?

END

如果我们有

CategoryId 1    
    ProductId 2    
    ProductId 8
    ProductId 20
    ProductId 29

CategoryId 2    
    ProductId 1    
    ProductId 3
    ProductId 20
    ProductId 29

CategoryId 3    
    ProductId 1    
    ProductId 8
    ProductId 20
    ProductId 29

结果是

CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8
CategoryId 1 | ProductId 29
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29

CREATE TABLE #mylist(product_id int NOT NULL PRIMARY KEY)
INSERT #mylist 
    SELECT Value FROM dbo.fn_split('1,8,29',',')

SELECT category_id,product_id
FROM    Product
WHERE   product_id IN (SELECT product_id FROM #mylist)

但是,当缺少一个产品id(如8)时,此查询将显示:

CategoryId 1 | ProductId 1
CategoryId 1 | ProductId 8 
CategoryId 1 | ProductId 29
CategoryId 2 | ProductId 1  --category2 should not be displayed,  
CategoryId 2 | ProductId 29 --  because product id 8 is missing....
CategoryId 3 | ProductId 1
CategoryId 3 | ProductId 8
CategoryId 3 | ProductId 29