>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.