This examples uses the Microsoft SQL Server 2008 example database AdventureWorks (http://msftdbprodsamples.codeplex.com/)

If you want to return all rows of a table when a parameter is null, use the TSQL function isnull:

declare @productModelID as int
set @productModelID = null


select
    p.*
from
    Production.Product p
where
    isnull(@productModelID, p.ProductModelID) = p.ProductModelID
order by p.ProductID