Empty IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Empty IN

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2004-04-20 : 13:17:56

Hello,
Maybe someone could help me with this.

I have 3 tables : tbl_Products, tbl_Categories, and tbl_ProductCategories.

As you may have guessed the products table holds the products and the productscategories holds the categories that a certain product falls under. A product can fall under many categories or not at all when it's a new product.

I am letting our users page products by passing in certain criteria. One of the being multiple, none, or 1 CategoryID. I've written a simple query

SELECT Products.Product_Name FROM tbl_Products AS Products, tbl_Categories AS Categories, tbl_ProductCategories AS ProdCat WHERE Products.Product_ID = ProdCat.Product_ID AND ProdCat.Category_ID = Categories.Category_ID AND Products.Product_ID = COALESCE(@ProductID, Product_ID) AND ProdCat.Category_ID IN (@CategoryID)

The problem I run into is that if I don't pass @CategoryID, then the IN statement wont return anything. I would like it to default to all categories. So that the IN statements returns products for every category unless of course I passed in a @CategoryID.

Hopefully I explained myself correctly.

Thank You,
Jose

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-04-20 : 13:20:41

WHERE @CategoryID is null or ProdCat.Category_ID = @CategoryID

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-20 : 13:30:10

[code]
if @CategoryID is null
begin
SELECT Products.Product_Name
FROM tbl_Products AS Products
inner join tbl_ProductCategories AS ProdCat
on Products.Product_ID = ProdCat.Product_ID
inner join tbl_Categories AS Categories
on ProdCat.Category_ID = Categories.Category_ID
WHERE Products.Product_ID = COALESCE(@ProductID, Product_ID)
AND ProdCat.Category_ID IN (@CategoryID)
end
else
begin
SELECT Products.Product_Name
FROM tbl_Products AS Products
inner join tbl_ProductCategories AS ProdCat
on Products.Product_ID = ProdCat.Product_ID
inner join tbl_Categories AS Categories
on ProdCat.Category_ID = Categories.Category_ID
WHERE Products.Product_ID = COALESCE(@ProductID, Product_ID)
end
[/code]

josethegeek
Starting Member

45 Posts

Posted - 2004-04-20 : 13:43:07

Thank You All!
I was hoping I wouldn't have to use IF statements. I might end up using ValterBorges suggestion, to help me modify the stored procedure I wrote for it.

Thanks Again,
Jose

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-20 : 14:06:36

quote:
Originally posted by josethegeek

Thank You All!
I was hoping I wouldn't have to use IF statements. I might end up using ValterBorges suggestion, to help me modify the stored procedure I wrote for it.

Thanks Again,
Jose

Just so you know, that way you can't have multiple @CategoryID's

josethegeek
Starting Member

45 Posts

Posted - 2004-04-20 : 14:37:44

quote:
Originally posted by RickD

quote:
Originally posted by josethegeek

Thank You All!
I was hoping I wouldn't have to use IF statements. I might end up using ValterBorges suggestion, to help me modify the stored procedure I wrote for it.

Thanks Again,
Jose

Just so you know, that way you can't have multiple @CategoryID's

RickD,
Yes, I know. I will convert it into an IN Statement.
Thanks.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-04-22 : 09:34:37

you can modify it to the following:

WHERE @CategoryIDS is null or ProdCat.Category_ID in (select * from dbo.CSVToTbl(@CategoryIDS))

Create the function below and make sure to setup the input size and output sizes and data types accordingly.

Create Function dbo.CsvToTbl ( @Array varchar(8000))
returns @Table table
(TblValue varchar(8000))
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(8000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources