Passing NULL SQL parameter values as SelectParameters bound to a Gridview

Question

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 varchar(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?

Answer 1

Have you tried adding this parameter?

CancelSelectOnNullParameter

Like this:

sd.CancelSelectOnNullParameter = false;