I can testify to the potential hit on checking for a parameter's NULL value. I once had a stored proc bring an entire server to its knees. Making one change - turning one proc into three procs that did not have to check for NULL values - instantly and completely fixed all problems.

You will not see this problem for checking for nulls all the time. There are times when checking for a null value makes sense. You should just be aware that it is a potential gotcha--especially since the problem may not be apparent in testing.