How to Select All from parameter list

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)

How to Select All from parameter list

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-18 : 11:34:11

I have a list of parameters that I want to use, but also have the option for an "ALL" parameter so that the user can select all from the parameter. The problem is that when I do a select all, it selects everything in that field, and there are some fields that I don't wait it to display. Only the ones within the parameter list.

The query I have below doesn't work that way.



SELECT ID, PRODUCT_CODE, DESCRIPTION, QTY
FROM PRODUCT
WHEN WAREHOUSE_ID = CASE @WAREHOUSE
	WHEN 'TOOLS' THEN 'TOOLS'
	WHEN 'PARTS' THEN 'PARTS' 
	WHEN 'RECYCLE' THEN 'RECYCLE'
	WHEN 'ALL' THEN WAREHOUSE_ID = 'TOOLS' 
	AND WAREHOUSE_ID = 'PARTS' 
	AND WAREHOUSE_ID = 'RECYCLE'
ORDER BY ID

It will work if I leave "WHEN 'ALL' THEN WAREHOUSE_ID", but does not work how I have it now. Anyone know the syntax to limit this to the parameter list only and pull everything in that field?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-18 : 15:50:01

so what you want is when your @WAREHOUSE = 'ALL' to display items where
WAREHOUSE_ID in ('TOOLS', 'PARTS', 'RECYCLE')?

Go with the flow & have fun! Else fight the flow

TimS
Posting Yak Master

198 Posts

Posted - 2005-05-18 : 16:44:35

SELECT ID, PRODUCT_CODE, DESCRIPTION, QTY
FROM PRODUCT
WHERE WAREHOUSE_ID = @WAREHOUSE OR 'ALL' = @WAREHOUSE
ORDER BY ID

TimS
Posting Yak Master

198 Posts

Posted - 2005-05-18 : 16:46:31

SELECT ID, PRODUCT_CODE, DESCRIPTION, QTY
FROM PRODUCT
WHERE WAREHOUSE_ID = COALESCE(NULLIF(@WAREHOUSE, 'ALL'), WAREHOUSE_ID)
ORDER BY ID

Tim S

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-18 : 17:40:58

Thanks for the response. Yes, I want it to select ('TOOLS', 'PARTS, 'RECYCLE') when the user selects 'ALL' as the parameter. I still want what I have, just that I can't get it to work when the user selects 'ALL' because if I only use the field "WHEN 'ALL' THEN WAREHOUSE", pulls all warehouse and not the ones I have within the parameter list.

Below query will pull all warehouse ID regardless of what I have for the parameter list which are ('TOOLS', 'PARTS, 'RECYCLE'). So I only want those 3 parameter from the list if the user picks all. As of right now then can pick only either one of the three, but they would like option to select all from those 3 and not all from whatever is in database.


SELECT ID, PRODUCT_CODE, DESCRIPTION, QTY
FROM PRODUCT
WHEN WAREHOUSE_ID = CASE @WAREHOUSE
	WHEN 'TOOLS' THEN 'TOOLS'
	WHEN 'PARTS' THEN 'PARTS' 
	WHEN 'RECYCLE' THEN 'RECYCLE'
	WHEN 'ALL' THEN WAREHOUSE_ID
ORDER BY ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources