How to get a gridview to show all table rows when

Question

The below works correctly and filters my gridview based on the text entered in my textbox.

When no text is entered into my textbox I get no results and cannot understand why.

MY QUESTION

How to get a gridview to show all table rows when no text is entered in the textbox?

MSSQL

@Search nvarchar(50)

SELECT  [table].[column]
FROM    [table]
WHERE   [table].[column] LIKE '%' + @Search + '%' OR COALESCE(@Search,'') = ''

MARKUP

<asp:TextBox ID="txtSearch" RunAt="Server" Text=""/>

<asp:SqlDataSource ID="sqlSearch" RunAt="Server" SelectCommand="spSearch" SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:ControlParameter Type="String" Name="Search" ControlID="txtSearch" PropertyName="Text"/>
  </SelectParameters> 
</asp:SqlDataSource>

RESULTS TO GRIDVIEW

I have tried and tested many methods found on here however all return 0 results when string is empty.

Answer 1

Instead of coalesce you may have a if block which checks if the @search parameter is empty or not as shown below..

if @search is not empty 
  SELECT  [table].[column]
  FROM    [table]
  WHERE   [table].[column] LIKE '%' + @Search + '%' OR COALESCE(@Search,'') = ''
else
  SELECT  [table].[column]
  FROM    [table]
  WHERE   [table].[column]

Answer 2

Look at CancelSelectOnNullParameter - by default, this prevents the select command from being called when a select parameter's value is null. After setting it to false, the select SP will then still get called even if the text box is empty, e.g.

<asp:SqlDataSource ID="sqlSearch" RunAt="Server" 
    CancelSelectOnNullParameter="false" ...>
</asp:SqlDataSource>

The SqlDataSource seems a bit misleading to me here, because TextBox.Text returns String.Empty when it's empty, not null, therefore I wouldn't expect to have to mess with CancelSelectOnNullParameter for a TextBox, but it seems we have to.