Nulls and values in stored proc 1

[Niv3k]

SQL 2000 -
I have three date fields, "Start", "Target", "Completed".

I need to let the user choose a value for each, or leave it blank.

The start date is required. All projects have a start date (relates to a creation date) The target can be TBD, which is null, and Completed can be blank, because it's not done yet.

IF the user enters a Completed date, then I want to return:

WHERE Completed < @Completed

But if they leave it blank, then I want to return ALL rows, I.E. no WHERE expression. Same with target.

I am thinking a user defined function, along the lines of

WHERE fTestDate(Completed, @Completed) AND fTestDate(Target, @Target)

Would return true or false, and I can determine the criteria through a simple IF statement in code.

My question is this:
Is there a way to do this all inside the stored proc, without adding another object to the database?

Kevin

 

[grahamm]

Assuming that the @completed variable is NULL if they did not fill in the completed field then this should do the trick:

SELECT ... FROM ...
WHERE Completed < @Completed or (@Completed is NULL)

Graham

 

[rac2]

Define an indicator variable instead of a function. The indicator variable is true when a criterion value is entered, and false when no value is entered.

CREATE PROCEDURE[proc_get_them](
@completed DATETIME
)
AS

/*This should have the value 0 by default.*/
DECLARE @have_date_completed BIT

/*This should evaluate to false if @completed is null.*/
IF(@completed)
   SELECT @have_date_completed = 1

/*This finds all rows or else rows meeting the criterion.*/
SELECT * FROM tracker
   WHERE @have_date_completed 
         OR completed < @completed

So if a value is supplied for Completed date then
@have_date_completed will be set to true and the WHERE clause will be true for all rows;
otherwise @have_date_completed will be false and the WHERE clause will only be true for rows with dates less than the Completed date supplied by the user.

I'm not 100% sure of the T-SQL code, but I have used this approach successfully.

 

[Niv3k]

Thanks to both of you! I have to give rac2 the star though, because I don't always want to get the records that are blank, and grahamm's approach would have done that.

Kevin