optional field in the where clause

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

SQL Server Development (2000)

optional field in the where clause

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-07-14 : 11:28:09

I have the following query as a stored proc. Essentially, this should execute with or without filtering on the txtmerchantname field. @merchant is an optional param (default value is null) and when it is not passed, I should just filter on account and cycle date. When it is passed, then filter should include the merchant name.

Here's an attempt, but I know the last part of the where clause is syntactically incorrect but I hope you get the idea of what I'm trying to do. The merchant name field is always populated and cannot be null.

select * from dbo.transactions
where txtAccountNum = @acct
and dteCycleDate = @date
and CASE WHEN @merchant IS NULL THEN txtMerchantName is not NULL
ELSE txtMerchantName = @merchant END

Thanks for any assistance.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-14 : 11:56:38

select * from dbo.transactions
where txtAccountNum = @acct
and dteCycleDate = @date
and txtMertchantName = COALESCE(@merchant, txtMerchantName)

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-07-14 : 12:00:51

Thanks. COALESCE works wonders. I'll read up on it.

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 12:40:47

Note that this method only works when the txtMerchantName column does NOT allows NULLs, if you need to cover that scenario too you could use:
AND (@merchant IS NULL OR txtMertchantName = @merchant)
but that method is less efficient than the COALESCE one

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources