handling null paramters in query

Question

Post reply

handling null paramters in query

HakunaMatata

SSCrazy

Points: 2610
More actions
October 18, 2010 at 2:05 pm

#225112

Hi,
I have written one parameterized query which looks like below -
select *
from table
where Column1 = Case when @parameter1 is NULL then Column1 else @parameter1 end
and Column2 = Case when @parameter2 is NULL then Column2 else @parameter2 end
In the above query, I have done validation for NULL value in parameter, means When there comes Value in the parameter then only use it else take all column values...
Now when I use this, it takes so long like around 30-40 minutes for query to execute and if I remove this null checking option and use heardcoded values for testing then query executes in just 10-20 seconds... !
Any other way to handle NULL parameters??? :crazy:
:rolleyes:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Sacheen (10/18/2010)

Hi,

I have written one parameterized query which looks like below -

select *

from table

where Column1 = Case when @parameter1 is NULL then Column1 else @parameter1 end

and Column2 = Case when @parameter2 is NULL then Column2 else @parameter2 end

I think the problem in your where clause. You are saying if @parameter is not null to get all rows.

if @parameter1 is null you code would the same as..

select * from table where Column1 = Column1

Or maybe this a way to work around a dynamic number of parameters being passed in?

That being said are there any indexes on these columns? Are there lots of rows in the table, etc etc etc.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass