I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):
select
* from dbo.employee where LastName>=COALESCE(@ln,lastname) andLastName<=COALESCE(@ln2,lastname) andFirstName>=COALESCE(@fn,firstname) andFirstName<=COALESCE(@fn2,firstname) andhiredate>=COALESCE(@hire,hiredate) andhiredate<=COALESCE(@hire2,hiredate) andcheckdate>=COALESCE(@chk,checkdate) andcheckdate<=COALESCE(@chk2,checkdate)The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.
I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it
Or is there a better way to use the COALESCE function
Thanks all!
COALESCE with parameters
ducky242
Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.
Once this has been done you can use:
WHERE
((checkdate >= COALESCE(@chk,checkdate) and checkdate <= COALESCE(@chk2,checkdate))As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.
Chris
D11
CMick
I would add a third parameter to the coalesce function. For instance COALESCE(@var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.
-Chris
MartinMalek
rnadella
Thanks!!!
I'll be studying that document for quite some time!