Share This!

Tuesday, December 11, 2012

SQL Server Matching on NULL parameter

So I have this query where I am trying to select on customer, unless the user doesn't enter a parameter for customer.  When they leave it blank, we want to see all customers.

So normally I would do it like this:

Select * from Orders where Customer=@customer And then to handle the null parameter I would change it like this:

Select * from Orders where ((Customer = @customer) OR (@customer is null))  This works great but then I came across this way of making it simpler.

Select * from Orders where Customer = isnull(@customer, Customer) The isnull()  effectively handles the case where the parameter (@customer) is null by replacing it with the content of the [Customer] data column, matching to itself!  Problem solved!


...

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

No comments:

Post a Comment

Contact Us

Name

Email *

Message *