Select All? - 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)

Select All?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-10 : 12:20:45

Hopefully you SQL or Reporting Services expert can help. I have a query in Reporting Services, that has 2 drop down parameter. I want to be able to add in a 3rd drop down that says "ALL", which uses both of the other 2 parameter.

SELECT ID, DESCRIPTION, BUYER, QTY
FROM WAREHOUSE
WHERE WAREHOUSE.ID = @WAREHOUSE

The 2 Parameters I'm using are "PARTS" and "TOOLS". I want to have a third that says "ALL" which uses both parameter. I think this could be done at SQL level, but not sure. I was thinking about "SELECT UNION" but not sure how that will be able to pull the right parameter. Any ideas?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-10 : 12:29:11

i think i see what you want. something like this?

SELECT ID, DESCRIPTION, BUYER, QTY
FROM WAREHOUSE
WHERE WAREHOUSE.ID = @WAREHOUSE
and parts = case when @parts = -1 then parts else @parts end
and tools = case when @tools = -1 then tools else @tools end

pass in the params. If they have not selected parts, then pass in -1 for that param. same for tools. if they have selected all, then pass in -1 for all of them

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-10 : 12:57:00

Thanks for the example, but I think I'm not making myself very clear. Let me make my correction. The Parameter @WAREHOUSE uses two value "PARTS" and "TOOLS", not 2 parameter "PARTS" and "TOOLS".

Example:

SELECT ID, DESCRIPTION, BUYER, QTY
FROM WAREHOUSE
WHERE WAREHOUSE.ID = @WAREHOUSE

From the drop down list, I have "PARTS" and "TOOLS" for the user to pick from. I want a third value that says "ALL", which will pull both of those value to be used within the parameter @WAREHOUSE.

So the result if picked "All":

SELECT ID, DESCRIPTION, BUYER, QTY
FROM WAREHOUSE
WHERE WAREHOUSE.ID = 'PARTS' AND WAREHOUSE.ID = 'TOOLS'

Let me know if this makes more sense.

Thanks.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-10 : 14:56:12

Perhaps this then?

SELECT
[ID], [DESCRIPTION], BUYER, QTY
FROM
WAREHOUSE
WHERE
WAREHOUSE.ID =
CASE @WAREHOUSE
WHEN 'TOOLS' THEN 'TOOLS'
WHEN 'PARTS' THEN 'PARTS'
WHEN 'ALL' THEN WAREHOUSE.ID
END

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-10 : 15:11:54

Awesome. That's what I was looking for.

A quick question though. If it's "All" from menu, can I put it like this so it just pull from "PARTS" and "TOOLS" value?

SELECT
ID, DESCRIPTION, BUYER, QTY
FROM
WAREHOUSE
WHERE
WAREHOUSE.ID =
CASE @WAREHOUSE
WHEN 'TOOLS' THEN 'TOOLS'
WHEN 'PARTS' THEN 'PARTS'
WHEN 'ALL' THEN WAREHOUSE.ID = 'TOOLS' AND WAREHOUSE.ID = 'PARTS'
END

It seems to be given me an error. Maybe something wrong with the syntax?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-10 : 15:13:59

Ignore my previous post. I mistype a little syntax error, but the one you provided works.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources