Suppose, you have to make search in the database table having optional parameters. For example you have some application with advanced search UI where user does not have to fill all search criteria. We can make it with one small trick - using ISNULL function.
CREATE PROC Search
@param1 INT = NULL
,@param2 INT = NULL
WHERE column1 = ISNULL(@param1, column1)
AND column2 = ISNULL(@param2, column2)
If the 1st param in ISNULL function is null - it returns 2nd parameter.