Passing NULL SQL parameter values as SelectParameters bound to a Gridview

The following SQL query WORKS perfectly in SQL Management Studio:

DECLARE @phone AS varchar(25)
SET     @phone = NULL 
DECLARE @firstname AS varchar(25)
SET     @firstname = NULL
DECLARE @lastname AS varchar(25)
SET     @lastname = NULL
DECLARE @email AS varchar(300)
SET     @email = NULL


 SELECT        id, lead_code, first_name + ' ' + last_name AS [name], lead_status, lead_source, date_entered, city, state, zip, gender, home_phone, call_back
  FROM            leads

 WHERE      (home_phone = @phone OR @phone IS NULL) AND
            (first_name = @firstname OR @firstname IS NULL) AND 
            (last_name = @lastname OR @lastname IS NULL) AND 
            (email = @email OR @email IS NULL)

ORDER BY date_entered DESC

Here's the top of the sproc:

ALTER PROCEDURE dbo.custom_LeadsGetAllLeadsSimpleSearchParams
    (
        @phone varchar(25) = null,
        @firstname varchar(25) = null,
        @lastname varchar(25) = null,
        @email va开发者_如何学编程rchar(300) = null
    )
AS
 SELECT        id.....

Running it this way gives me all 2100 rows in my DB, when I change one of the SET commands to search for a name, or email address, it returns JUST those rows, I think my SQL is (while maybe not awesome) working.

The problem is in the .NET piece.

From a codebehind of the the page I'm doing this:

                sd.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
                sd.SelectCommand = "custom_LeadsGetAllLeadsSimpleSearchParams";

                sd.SelectParameters.Add("@phone", strPhone);
                sd.SelectParameters.Add("@firstname", strFirstName);
                sd.SelectParameters.Add("@lastname", strLastName);
                sd.SelectParameters.Add("@email", strEmail);
                gvLeads.DataBind();

the string variables are passed as null when null. When I watch the sd.SelectParameters collection in the debugger, I'm seeing the parameter value as null when and where needed. The problem is that when the databind occurs, the grid renders with no data. I just get the gridview no rows text that I have set. Ideas?

Have you tried adding this parameter?

CancelSelectOnNullParameter

Like this:

sd.CancelSelectOnNullParameter = false;

sd.CancelSelectOnNullParameter = false;

Have you tried adding this to each argument:

sd.SelectParameters["@phone"].ConvertEmptyStringToNull = true;

I don't know if ConvertEmptyStringToNull converts the parameter value to NULL or DbNull.Value, but you might try using DbNull.Value instead of NULL.

EDIT: On second thought, how about including the parameters only when they're available:

if (!String.IsNullOrEmpty(strEmail))
    sd.SelectParameters.Add("@email", strEmail);

I think this solution will work, because you're just excluding it altogether when it's null.