Dynamic Parameter with '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

Development Tools

Reporting Services Development

Dynamic Parameter with 'ALL'?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-25 : 17:42:08

I have two SP that one pulls the data and another one is use for the Parameter. I want to have an option where the user can select everything regardless of parameter eg. 'ALL'. I'm sure this can be done at the SQL level. Anyone might be able to help?

SP1 pulls name to be use as parameter:
[CODE]
SELECT DISTINCT BUYER_NAME
FROM BUYER
[/CODE]

SP2 Pulls all other information base on parameter:

[CODE]
SELECT WO.WO_ID, P.PART_ID,
WO.DESCRIPTION, WO.PRICE,
WO.QTY, B.BUYER, WO.LOCATION
FROM BUYER B INNER JOIN PARTS P
ON B.ID = P.PART_ID INNER JOIN WORKORDER WO
ON WO.WO_ID = B.ID
WHERE B.BUYER = @BUYER

[/CODE]

With the example, how would I set it so that I can have an 'ALL' in there so that it can be displayed in RS.

I was thinking about using a case for this but not sure how that would work in RS when I set the Parameter to all and not able to have the rest of the other parameters from the first SP.

example:



SELECT 	WO.WO_ID, P.PART_ID, 
	WO.DESCRIPTION, WO.PRICE, 
	WO.QTY, B.BUYER, WO.LOCATION
FROM 	BUYER B INNER JOIN PARTS P
	ON B.ID = P.PART_ID INNER JOIN WORKORDER WO
	ON WO.WO_ID = B.ID
WHERE B.BUYER = CASE @BUYER
	WHEN 'ALL' THEN B.BUYER
	END

Any help is appreciated. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-25 : 17:46:00

Your parameter query would become:

SELECT DISTINCT BUYER_NAME
FROM BUYER
UNION ALL
SELECT 'All'

Your other query would become:

SELECT WO.WO_ID, P.PART_ID,
WO.DESCRIPTION, WO.PRICE,
WO.QTY, B.BUYER, WO.LOCATION
FROM BUYER B INNER JOIN PARTS P
ON B.ID = P.PART_ID INNER JOIN WORKORDER WO
ON WO.WO_ID = B.ID
WHERE B.BUYER = CASE WHEN @BUYER = 'All' THEN B.BUYER ELSE @BUYER END

Tara

jhermiz

3564 Posts

Posted - 2005-05-25 : 21:24:03

Chris,

I cant believe you're posting this again, you've asked this question I would say 2 times in the past and the same answers were given to you...

Come on man..you are better than that!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-26 : 00:43:05

It did sound familiar. Wasn't sure who asked it though.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-26 : 10:07:51

I did ask about it before, but it's only static list and not dynamic. I was curious how to do it with a dynamic list, which Tara provided the example.

So thanks Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources