jpratt (6/1/2009)
Instead of NULL (dates are an exception - see below), use a default value appropriate for the data type. I usually use 0 for number data types and empty string ('') for character data types. Then evaluate the parameter in the WHERE clause as ISNULL(@NumberParm, 0) or ISNULL(@StringParm, '').For dates, use NULL as the default and then use the ISDATE() function to make sure you get a real date. Since both '' and NULL will cause ISDATE(@DateParm) to return 0, it is safe and robust.
I disagree with your comment. You have a problem when using ISNULL and default values and that is you can't have null values in columns and search by those columns too. If you do decide to not allow null values in tables then you have to use default values for columns too, which values must be in the referenced tables too. I don't see this is logical and usefull.
Having nullable parameters is very normal to use and i can't see any problem.