Why does the following bring back data only when both parameters are set?

Question

The two procedures, SearchMaster and SearchDetails work if I run it in Sql Management Studio and SearchMaster even runs fine when I am Testing the Query when I set up the stored procedure in design view, but when I run it, no rows are created if I enter just info for the frn or business. If I enter for both, I do get data if there is a match. Are the parameters etup correctly? Also, if I am initializing the parameters to null in the procedure, is it still necessary to use ConvertEmptyStringToNull?

Here is the SearchMaster StoredProcedure:

CREAETE PROCEDURE SearchMaster
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT h.Business,
       hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE (@business IS NULL OR h.Business like '%' + @business + '%') 
AND (@frn IS NULL OR hrl.frn = @frn)

Here is the SearchDetails StoredProcedure:

CREATE PROCEDURE SearchDetails
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT hrl.call 
FROM registration hrl 
INNER JOIN holder h ON h.call = hrl.call
WHERE (@business IS NULL OR h.Business LIKE '%' + @business + '%') 
  AND (@frn IS NULL OR hrl.frn = @frn)

Here is the SqlDataSource for the SearchMaster procedure:

<asp:SqlDataSource ID="sqlDsDetails" 
               runat="server" 
               ConnectionString="<%$ ConnectionStrings:cnxString %>
               SelectCommandType="StoredProcedure" 
               SelectCommand="SearchMaster">
<SelectParameters>
<asp:ControlParameter Name="business" ControlID="txtBusiness" 
                  Type="String" PropertyName="Text"  
                  ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="frn" ControlID="txtFRN" 
                  Type="String" PropertyName="Text"
                  ConvertEmptyStringToNull="true"/>
</SelectParameters>
</asp:SqlDataSource>

Here is the SqlDataSource for the SearchDetails procedure:

<asp:SqlDataSource ID="sqlDsDetails" 
               runat="server" 
               ConnectionString="<%$ ConnectionStrings:cnxString %>
               SelectCommandType="StoredProcedure" 
               SelectCommand="SearchDetails">
<SelectParameters>
<asp:Parameter Name="frn" Type="String" DefaultValue="" 
           ConvertEmptyStringToNull="true" />
<asp:Parameter Name="business" Type="String" DefaultValue="" 
           ConvertEmptyStringToNull="true" />
</SelectParameters>
</asp:SqlDataSource>

Here is the button click that binds the SqlDsMaster:

protected void btnSearch_Click(object sender, EventArgs e)
{
    sqlDsMaster.DataBind();
}

Here is the gvMaster_RowCreated that creates the rows for the details:

protected void gvMaster_RowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        SqlDataSource ctrl = 
        e.Row.FindControl("sqlDsDetails") as SqlDataSource;

        if (ctrl != null && e.Row.DataItem != null)
        {
            ctrl.SelectParameters["frn"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["frn"].ToString();

            ctrl.SelectParameters["business"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["business"].ToString();
         }
     }
 }

I ran the SQL Profiler and when only one parameter was entered, the profiler did not even show the procedure running. When I entered both parameters, the procedure was shown running in the profiler.

Answer 1

My guess would be that it is still passing in an empty string for what you consider the null case. You could rule this out by doing:

CREATE PROCEDURE SearchDetails
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS

SELECT @business = NULLIF(@business, ''),
       @frn = NULLIF(@frn, '')

 SELECT hrl.call // etc

If it starts working, it was passing in empty strings.

Answer 2

I suspect Marc Gravell is right on the money.

Try the following tests:

 SearchMaster @business = null        , @frn = null
 SearchMaster @business = 'something' , @frn = null
 SearchMaster @business = 'something' , @frn = 'something'
 SearchMaster @business = null        , @frn = 'something'
 SearchMaster @business = 'something' , @frn = 'something'
 SearchMaster @business = 'something' , @frn = ''
 SearchMaster @business = ''          , @frn = 'something'

Note that the last 2 will not return anything - unless you have blank businesses and frn in the DB ;)

If you place the select statement from Marc just after the AS line your statement it should work as you expect it to.

So something like this:

CREATE PROCEDURE SearchMaster
@business nvarchar(300) = NULL,
@frn nvarchar(10) = NULL
AS
SELECT @business = NULLIF(@business,'') , @frn = NULLIF(@frn,'')

SELECT h.Business,
       hrl.frn
FROM registration hrl
INNER JOIN holder h on h.call = hrl.call
WHERE (@business IS NULL OR h.Business like '%' + @business + '%') 
AND (@frn IS NULL OR hrl.frn = @frn)

And make sure to set CancelSelectOnNullParameter="False" on your DataSource