Conditional parameters (kinda)

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)

Conditional parameters (kinda)

Author

Topic

ultraman
Starting Member

18 Posts

Posted - 2005-08-05 : 13:25:06

Hi !

I'm writing a SP that search the DB for records matching about 10 criteria. Starts like this :


CREATE PROCEDURE spHD_Demande_GetList
	@DEM_id int,
	@USA_id int,
	@GRP_SUP_ID int,
	@PRIORITE_ID int
AS
SELECT * 
FROM MyTable 
WHERE UsaID = @USA_id 
  AND DemandID = @DEM_id
  AND GroupID = @GRP_SUP_ID
  AND PriorityID = @PRIORITE_ID

But what if the user wants ALL demands for a user no matter what the other parameters are ? In other words, I would only care about the @USA_id parameter, the rest would not be taking care of. I'd like to avoid using a big select case block, because my actual SP has a lot more parameters that that one. Is there a way to do such a thing ?

--------------
Ultraman

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 13:49:00

Pass the "don't care" parameters as NULL?


CREATE PROCEDURE spHD_Demande_GetList
	@DEM_id int,
	@USA_id int,
	@GRP_SUP_ID int,
	@PRIORITE_ID int
AS
SELECT * 
FROM MyTable 
WHERE (UsaID = @USA_id OR @USA_id IS NULL)
  AND (DemandID = @DEM_id OR @DEM_id IS NULL)
  AND (GroupID = @GRP_SUP_ID OR @GRP_SUP_ID IS NULL)
  AND (PriorityID = @PRIORITE_ID OR @PRIORITE_ID IS NULL)

EDIT: OK so I've read the question now and I answered a different one! But I think the concept will stretch to what you want

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-05 : 13:50:10

How about this

http://weblogs.sqlteam.com/brettk/archive/2004/05/05/1312.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 13:53:31

#Table for a @Parameter eh?

Not seen that done before Brett, but I'm going to keep it in mind, thanks.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources