There are two ways to resolve this. One is solution but it's not very flexible. The other is a workaround but can be used in more situations. First the solution. If the parameter is used as a minimum or maximum value check, you can totally ignore the value if it's null.
Here is an example of the first solution. Let's say we want to write a stored procedure to get all records from an Activity table that occured from one point in time to another. In this case we want to include all records if the boundry variable is not specified. In the example below we simply do this by checking for NULL and using the logical OR.
CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME
AS
BEGIN
SELECT * FROM Activity
WHERE (@From is null or LogTime >= @From)
and (@To is null or LogTime <= @To)
END
Another way to do this that is more flexible is to check if the parameter is null and in that case set it equal to whatever value you wish.
For example, in the code below I check if the @To variable is null and if it is I set it equal to the current time.
CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME
AS
BEGIN
IF @To is null
BEGIN
SET @To = GetDate()
END
SELECT * FROM Activity
WHERE (@From is null or LogTime >= @From)
and LogTime <= @To
END
