>>Hi,
>>
>>How do you perform comparison of an integer (or smallint) type column to a value with COALESCE?
>>
>>For example, when in a stored procedure a parameter is optional I set the default value to NULL (if not passed). Then I use the following WHERE expression to return all rows if value not passed.
>>
>>
>>select * from mytable where Col_IntType = COALESCE(@param1, Col_IntType)
>>
>>
>>The above works but I was wondering if there is a better way.
>>
>>TIA.
>
>I would write this condition as
>
>select * from myTable where (@Param1 IS NULL or Col_IntType = @Param1)
>
>In the first case, the records where Col_IntType are NULL are not returned. In the second case they are returned. If you don't have records with NULL values in your table, then both where expressions will return the exactly same results.
Are you saying that the Where clause I posted ("where Col_IntType = COALESCE(@param1, Col_IntType)" will not return records with NULL value? Why not? If @param1 is NULL than the expression will evaluate to Col_IntType = Col_IntType; and wouldn't it be always True regardless of the value in the field?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
