Parameterized Query Performance Help!

[sqlDon]

I am having trouble with paramterized query. This will end up being a stored procedure in the end, but for now I am working with it in Query Analyzer.

DECLARE

@Tax_ID varchar(9)
@Badge_ID varchar(9)

SET @Tax_ID = '1234'
SET @Badge_ID = '5678'

Select * from Test where tax_id = @Tax_ID and badge_id = @Badge_ID


-This executes and returns 17 records in 1 second


However, the user may choose one or all parameters. So, I need to check for NULL.

Select * from Test where tax_id = ISNULL(@Tax_ID, tax_id) and badge_id = ISNULL(@Badge_ID,badge_id)


-This executes and returns 17 records in almost 2 MINUTES!!!

The table is indexed by Tax_ID and Badge_ID.

Looking at the Execution Plan reveals that it is doing a clustered index scan.
How can I optimize the query without doing a bunch of structured IF..ELSEIF statements? I also tried using the COALESCE function, but it did not improve the speed.


TIA
Don

[Fausto Guerini]

I normally write:

Select * from Test
 where (tax_id = @tax_id or ISNULL(@Tax_ID, '')='') 
 and (badge_id = @badge or ISNULL(@Badge_ID,'')='')

any better?
Hi
Fausto